cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.