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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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