CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Shan
Shan

Reputation: 31

Power BI - Calculate difference between two date/time values in Hours (> 24 hours) and then Average

I've a Power BI published dataset and using DAX I'm trying to calculate difference(Total Time Taken) between Start datetime and End datetime for each task and then Average Total Time Taken in Hours for all tasks. I've tried few methods and all of them have failed. I don't have the option to edit queries or Transform data as its a published dataset that I've connected to, all i can do is create measures that gives the time difference in Hours even it is over 24 hours and then do the average like in the example below;

enter image description here

Tasks     Start DateTime         End DateTime        Total Time Taken
1     10/08/2020 10:30:00    11/08/2020 10:30:00        24:00:00
2     10/08/2020 11:30:00    12/08/2020 10:30:00        47:00:00
3     12/08/2020 10:30:00    14/08/2020 10:30:00        48:00:00
4     10/08/2020 10:30:00    16/08/2020 10:30:00        144:00:00

Average for all Tasks 65:45:00

So need your guys help in cracking a solution. Thanks in advance.

Upvotes: 3

Views: 21706

Answers (4)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

Late for the party but with a one-liner:

FORMAT( DIVIDE( [DurationInSeconds] ) , 86400), "HH:mm:ss" )

It returns desired solution in hours, minutes, and seconds. If Duration is longer than 1 day (86400 sec), it just trims the day part.

HH instead of hh gets 24h format time.

https://learn.microsoft.com/en-us/dax/format-function-dax

Upvotes: 0

sam
sam

Reputation: 1304

The below Measure will get you the exact output like seen in your image:

Total Hours = 
var val = SUBSTITUTE(averagex(Sickness,DATEDIFF(Sickness[Start_Date],Sickness[End_Date],HOUR)),".",":")&":00:00"
var ln = LEN(val) - LEN(SUBSTITUTE(val,":",""))
return
IF(ln <= 2,val,LEFT(val,LEN(val)-3))

the Output of the above code is shown below:

enter image description here

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Create a measure as below-

duration_average = 

VAR total_second = 
AVERAGEX(
    your_table_name,
    DATEDIFF(your_table_name[Start DateTime],your_table_name[End DateTime],SECOND)
)

VAR DAXDay   =  INT(total_second/(24*60*60))
VAR DAXHours =  MOD(INT(total_second/(60*60)),24)
VAR DAXMin   =  MOD(INT(total_second/60),60)
VAR DAXSec   =  MOD(total_second,60)

RETURN DAXDay &" Day "& DAXHours &" Hour "& DAXMin &" Minute "& DAXSec &" Second"

Output will be as below. You can also create your own required formatted output from the raw values.

enter image description here

Upvotes: 2

Himanshu Agrawal
Himanshu Agrawal

Reputation: 271

You can use below formula to display Total_time_taken for each task:

Total_time_taken = DATEDIFF(CALCULATE(VALUES(Time_Track[Start_Date_Time]), FILTER(Time_Track, Time_Track[Task] = SELECTEDVALUE(Time_Track[Task]))),CALCULATE(VALUES(Time_Track[End_Date_Time]), FILTER(Time_Track, Time_Track[Task] = SELECTEDVALUE(Time_Track[Task]))), HOUR)

enter image description here

Calculation on Average time_taken is getting little tricky after this so I have used the below formula for calculating the average time. It displays correct value if you want to round you avg to hours but if you want to show minutes it shows 66.75 instead of your 66:45 because I cannot change the datatype of measure to time.

Average_Time_Taken = AVERAGEX(Time_Track, Time_Track[Total_time_taken])

enter image description here

Upvotes: 0

Related Questions