cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors