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 III
Super User III

@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 III
Super User III

@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 III
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(
        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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,120)