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.

Reply
JamHam
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.

Seconds - Cards.png

 

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.

Minutes - Cards.png

 
 
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
Greg_Deckler
Super User
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...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
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...

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors