Forum Discussion
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 Time | End Time | Expected Result in HH: MM |
| A | 01:30:00 | 02:00:30 | 00:30:00 HH: MM |
| B | 11:00:00 | 03:30:00 | 04: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
MYDATASTORY, try this measure:
Time Diff = SUMX ( TestData, TestData[End Time] - TestData[Start Time] )
4 Replies
MYDATASTORY, try this measure:
Time Diff = SUMX ( TestData, TestData[End Time] - TestData[Start Time] )So that works, but then the time won't sum in a table or matrix. How to fix this? Please and thank you.
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 )Thank you v v much. It works. It works well. It works really well! IT WORKS!!! 🤣
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.
