Reply
Highlighted
Member
Posts: 100
Registered: ‎10-25-2017

Average of difference in hh:mm format

Hi All,

I have 2 date fields where I am calculating difference between them like below

 

datediff(Replen_stats[create_date_time],Replen_stats[mod_date_time],minute)

 

Now if it has 5 rows for same so I want to calculate average of minutes for 5 rows in hh:mm format

say sum of minutes for 5 rows is 670 minutes then the evrage should be 670/5=134 minures is average which should show 02:14 

 

Any ideas please

 

Thanks

 

 

 

New Contributor
Posts: 607
Registered: ‎02-29-2016

Re: Average of difference in hh:mm format

Hi @srlabhe123

 

You could capture the entire calculation in a measure and avoid the DATEDIFF calculation:

Average Time Difference =
AVERAGEX (
    Replen_stats,
    Replen_stats[mod_date_time] - Replen_stats[create_date_time]
)
    + TIME ( 0, 0, 0 )

(The TIME ( 0, 0, 0 ) is there to ensure the measure is cast as a time value.)

 

Then ensure the Measure Format is HH:mm.

 

Regards,

Owen

Member
Posts: 100
Registered: ‎10-25-2017

Re: Average of difference in hh:mm format

This is working of the diffrenec betwenn dates is less than 24 Hrs but if dates are like below see the value of measure above in first colum.Capture1.JPG

 

Its just taking difference between time stamps and not dates.

Member
Posts: 100
Registered: ‎10-25-2017

Re: Average of difference in hh:mm format

This is working of the diffrenec betwenn dates is less than 24 Hrs but if dates are like below see the value of measure above in first colum.Capture1.JPG

 

Its just taking difference between time stamps and not dates.

New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: Average of difference in hh:mm format

You could use this to caluclate the hours and then use datediff to calculate the Days and then build a text measure that displays Days*24+HOUR(Average Time Difference)&":"&MINUTE(Average Time Difference)

Member
Posts: 100
Registered: ‎10-25-2017

Re: Average of difference in hh:mm format

R=This too is not working as Days from ha sto be column and not measure. And then the calculation is shwoing wrong

Days*24+HOUR(Average Time Difference)&":"&MINUTE(Average Time Difference)

New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: Average of difference in hh:mm format

What if you do something similar to "Average Time Difference" called "Average Days Difference" where  you add + Date(0,0,0) to at the end and use [Average Days Difference]*24?

Member
Posts: 100
Registered: ‎10-25-2017

Re: Average of difference in hh:mm format

No that wont help me to display as I want to have the difference in hh:mm format only and not in days

New Contributor
Posts: 444
Registered: ‎02-15-2018

Re: Average of difference in hh:mm format

Have two Measures [Average Time Difference] and [Average Day Difference] then a third Measure [Hour Difference] to display the result.

 

Hour Difference = [Average Day Difference]*24+HOUR([Hour Difference])&":"&MINUTE([Hour Difference])

Member
Posts: 100
Registered: ‎10-25-2017

Re: Average of difference in hh:mm format

 

what would be formula for Avg Day, Avg Time difference then?