cancel
Showing results for
Did you mean:
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
Super User III

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(
"_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!
4 REPLIES 4
Super User III

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(
"_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!
Frequent Visitor

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)"

Super User III

@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(
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!
Frequent Visitor

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!

Announcements