Issue with Dax and calculating Difference in Minutes

JamHam — Tue, 27 Jun 2023 18:55:39 GMT

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,

Re: Issue with Dax and calculating Difference in Minutes

Greg_Deckler — Tue, 27 Jun 2023 20:09:21 GMT

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

Re: Issue with Dax and calculating Difference in Minutes

JamHam — Tue, 27 Jun 2023 21:58:19 GMT

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

topic Re: Issue with Dax and calculating Difference in Minutes in DAX Commands and Tips

Issue with Dax and calculating Difference in Minutes

Re: Issue with Dax and calculating Difference in Minutes

Re: Issue with Dax and calculating Difference in Minutes