cancel
Showing results 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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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.

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