cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsamms
Frequent Visitor

Average of the difference between two date columns (not single date entries)

In building a annual statistics section of my Power App, I've been asked to calculate the average timeframe between two column of dates, which encompasses about 200 rows of entries (yearly).  We'll say Date1, which is ordered, and Date2, which is shipped.  The intent is to figure out the average time between the two dates for the numerous entries throughout the year.  While my amazing ability to google search has allowed me to master this DateDiff functionality between two single date entries, I could use some help here given that I'm attempting to average the date difference between two entire columns of dates.  Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@bsamms 

Sure thing...I am not sure if you are wanting to average the minute difference or hour, or day - so substitute in below solution as needed.

 

In general, your formula will look like this (let's say this is on a Label Text property to show the average):

 

Average(
    AddColumns(yourDataSource,
        "_diff", DateDiff(Date1, Date2, Hours)
    ),
    _diff
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
Super User

@bsamms 

Sure thing...I am not sure if you are wanting to average the minute difference or hour, or day - so substitute in below solution as needed.

 

In general, your formula will look like this (let's say this is on a Label Text property to show the average):

 

Average(
    AddColumns(yourDataSource,
        "_diff", DateDiff(Date1, Date2, Hours)
    ),
    _diff
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Thank you @RandyHayes !  Not sure why, but it does errors out when I attempt to make it "Days" instead of "Hours".  My analytic page calculates the data based on a selected year (input), so I modified your solution to my variable generator (below) with necessary filtering, and then the displayed result to be divided by 24 so as to be displayed as "Days".  Thanks for the quick help!
//On Generate Stats button
Set(varTime1,
Average(
AddColumns(Filter(CaseTracker_data, CaseType.Value = "A", StartsWith(CaseNumber, varYear)),
"_diff", DateDiff(Date1, Date2, Hours)
),
_diff+1
));
//On the label
Text(Value(varTime1/24),"[$-en-US]0.00") & " (Average)"

RandyHayes
Super User
Super User

@bsamms

Not sure why but there appears to be a +1 in your formula.  That's not going to work.

Your formula should be this (and you can just put this on the label text property, unless for some reason you need a variable:

Text(
    Average(
        AddColumns(
            Filter(CaseTracker_data, CaseType.Value = "A", StartsWith(CaseNumber, varYear)),
            "_diff", DateDiff(Date1, Date2, Days)
        ),
        _diff
    ) / 24, 
    "0.00"
) & " (Average)"

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I was attempting to rectify the DateDiff only counting the days in-between both dates, and not either date as being part of the total count.  I see the flaw in my logic, and it turns out the +1 did not change the resulting number anyway.  Thanks again!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,382)