Average of difference in hh:mm format

[Anonymous]

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

[OwenAuger]

Hi @Anonymous

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

[Anonymous]

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.

Its just taking difference between time stamps and not dates.

[Seward12533]

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)

[Anonymous]

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)

[Seward12533]

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?

[Anonymous]

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

[Seward12533]

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

[Anonymous]

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

[Seward12533]

OK I looked at this more closely and tested it on a data set that had Order and Delivery Dates that I understood.

I first added several columns to the data (could do it in DAX but using columns makes the DAX much simpler.

Deliver Delay = IF([DELIVERY_DATE],[DELIVERY_DT]-[ORDER_DT]) // Calculate delay IF Part has been delivered
Delivery Delay Days = INT([Deliver Delay]) // Extract Days 
Delivery Delay Hours = HOUR([Deliver Delay])/24 // Calculate decimal fraction of Number of Hours

I ignored Min,Sec and extacted Hours as decimal value so 12hrs would = .5 Days

Then I created some Measures

Total Delay Days Part = sum(BaanV_TEA_Sales_Orders[Delivery Delay Days])*24
Total Delay Hours Part = sum(BaanV_TEA_Sales_Orders[Delivery Delay Hours])
Total Delay = [Total Delay Days Part]+[Total Delay Hours Part]
Total Items = CALCULATE(COUNTROWS(BaanV_TEA_Sales_Orders),NOT(ISBLANK((BaanV_TEA_Sales_Orders[DELIVERY_DATE]))))
Average Delay Hours = ([Total Delay])/[Total Items]

Note I could probably have used AverageX or something but I'm wary of average of averages, and I honestly need to strenthen my understanding of the "X" functions. So I just calculated Total Hours and Total Items seperately and calculated it.

Hope this helps and thanks it was an interesting challenge.  I normally dont' work about Minutes so I didn't realize that DateDiff didn't handle them the way I expected.

[Anonymous]

Thanks for detailed reply but could not understand why Total Items are used here ? This may not be applicable for my use case rather I have just Mod Date Time and Create Date time for Tasks. Wanted to calculate the diffence bewteen those 2 dates in hh:mm format.

[Seward12533]

Basically you can't do what you want the way PowerBI (and Excel) handle times.  It will just always return the number of hours different ignoring the number of days.

My solution is a workaround which

• calculates this for each row in the data using a colums to calculate the Number of Days, and Hours
• coverts hours to decimal fractions of day
• converts days to hours by multiplying by 24
• Calculates the total hours as a decimal fraction

This allows you so Sum, Average or whatever you want the total delay as a decimal fraction.

If you want to then display this decimal fraction in HH:MM format you can do this with a text meausure

Total Delay  HH:MM =
   VAR H_Part = TRUNC([Total Delay]) VAR M_Part = INT(([Total Delay]-H_Part)/*24) RETURN
   H_Part&":"&M_Part

You can construct simlar meausres for Average Delay or other meausres.

Thinking about it more a simpler approach would be to take the differnece as date/time and then use variant of the above text meausre desconstructon to diplay in HH:MM format but woudl need to convert the integer part into hours.  but not sure if will work with aggegration.  I know this works, let me know what you end up as a solution.

[Anonymous]

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.

Its just taking difference between time stamps and not dates.