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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Issue with Dax and calculating Difference in Minutes

Hello,

I am running into an issue when building a difference measure for Minutes. Below are the calculations for building the Current/Previous/Current Vs. Previous Seconds.

Current_MTD_Seconds = TOTALMTD([Average_Seconds_Day],DATESMTD([Date]))

Previous_MTD_Seconds = CALCULATE([Average_Seconds_Day], PREVIOUSMONTH(DATESMTD([Date])))

CvsP_MTD_Seconds = [Current_MTD_Seconds]-[Previous_MTD_Seconds]

These calculations are behaving as expected and are displaying the correct numbers.

But when I convert the seconds to minutes using the following Dax measure. The Current vs Previous measure is giving the incorrect time.

Current_MTD_Minutes = FORMAT(INT(
IF(MOD([Current_MTD_Seconds],60)=60,0,MOD([Current_MTD_Seconds],60)) +
IF(MOD(INT([Current_MTD_Seconds]/60),60)=60,0,MOD(INT([Current_MTD_Seconds]/60),60)*100) +
INT([Current_MTD_Seconds]/3600)*10000), "00:00")

Previous_MTD_Minutes = FORMAT(INT(
IF(MOD([Previous_MTD_Seconds],60)=60,0,MOD([Previous_MTD_Seconds],60)) +
IF(MOD(INT([Previous_MTD_Seconds]/60),60)=60,0,MOD(INT([Previous_MTD_Seconds]/60),60)*100) +
INT([Previous_MTD_Seconds]/3600)*10000), "00:00")

CvsP_MTD_Minutes = FORMAT(INT(
IF(MOD([CvsP_MTD_Seconds],60)=60,0,MOD([CvsP_MTD_Seconds],60)) +
IF(MOD(INT([CvsP_MTD_Seconds]/60),60)=60,0,MOD(INT([CvsP_MTD_Seconds]/60),60)*100) +
INT([CvsP_MTD_Seconds]/3600)*10000), "00:00")

For CvsP_MTD_Minutes I would expect the value to be -00:05. But it is giving me a value of -40:45.

This is my first time working with Power BI and Time calculations. So not sure where to start to troublshoot this issue.

Thank you,
1 ACCEPTED SOLUTION
Super User

@JamHam See if this helps: Chelsie Eiden's Duration - Microsoft Fabric Community

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@JamHam See if this helps: Chelsie Eiden's Duration - Microsoft Fabric Community

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@Greg_Deckler - Thank you so much! This worked perfectly for what I was looking to accomplish.

Helpful resources

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors