Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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