cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

1 ACCEPTED SOLUTION
Super User

@MYDATASTORY, try this measure:

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

Proud to be a Super User!

4 REPLIES 4
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.

Super User

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

``````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
VAR vHoursFormatted =
IF ( LEN ( vHours ) = 1, "0" & vHours, "" & vHours )
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 )``````

Proud to be a Super User!

Frequent Visitor

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.

Super User

@MYDATASTORY, try this measure:

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

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors