Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MYDATASTORY
Resolver I
Resolver I

DAX Time calculation between two timestamp

Hi,

Kindly advice with actual Dax formulae to calculate the time difference.

If you're able to provide a sample of DAX based on below dammy data will be great.

The data will be presented in tabular form and also in visualization where Total Duration (HH: MM) will be grouped by ID and Month. Much appreciated in advance.

 

ID Start TimeEnd TimeExpected Result in HH: MM
A01:30:0002:00:3000:30:00 HH: MM
B11:00:0003:30:0004:30:00 HH:MM

Expected Answer A on the table

Total Time: For A=00:30:00HH:MM

Total Time: For B=04:30:00HH:MM
Expected Answer B on the table

Total Time for all IDs =(Total Time: For A=00:30:00HH:MM)+(Total Time: For B=04:30:00HH:MM)=05:00HH:MM

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@MYDATASTORY, try this measure:

 

Time Diff = 
SUMX ( TestData, TestData[End Time] - TestData[Start Time] )

 

DataInsights_0-1601656980364.png

 

DataInsights_1-1601657011072.png

 

DataInsights_2-1601657026994.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Caroline_1900
Frequent Visitor

So that works, but then the time won't sum in a table or matrix. How to fix this?  Please and thank you.Screenshot 2020-11-30 061321.pngScreenshot 2020-11-30 061627.png

@Caroline_1900,

 

Try this measure. It's based on a measure in the link below.

 

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

 

Time Diff = 
// Given a number of seconds, returns a format of "hh:mm"
VAR vDuration =
    SUMX ( TimeTracker, DATEDIFF ( TimeTracker[Start_Time], TimeTracker[End_Time], SECOND ) )
// There are 3,600 seconds in an hour
VAR vHours =
    INT ( vDuration / 3600 )
// There are 60 seconds in a minute
VAR vMinutes =
    INT ( MOD ( vDuration - ( vHours * 3600 ), 3600 ) / 60 )
// These intermediate variables ensure that we have leading zeros concatenated onto single digits
// Hours with leading zeros
VAR vHoursFormatted =
    IF ( LEN ( vHours ) = 1, "0" & vHours, "" & vHours )
// Minutes with leading zeros
VAR vMinutesFormatted =
    IF ( LEN ( vMinutes ) = 1, "0" & vMinutes, "" & vMinutes )
// Now return hours and minutes with leading zeros in the proper format "hh:mm"
VAR vResult =
    vHoursFormatted & ":" & vMinutesFormatted
RETURN
    IF ( vResult = ":", BLANK(), vResult )

 

DataInsights_0-1606835439929.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you v v much.  It works.  It works well.  It works really well!  IT WORKS!!! 🤣 Screenshot 2020-12-04 081431.png

Please accept my apologies for the delay in responding.  PowerApps design is not my full-time work. But this project is something that I have to make work for me to continue.

DataInsights
Super User
Super User

@MYDATASTORY, try this measure:

 

Time Diff = 
SUMX ( TestData, TestData[End Time] - TestData[Start Time] )

 

DataInsights_0-1601656980364.png

 

DataInsights_1-1601657011072.png

 

DataInsights_2-1601657026994.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.