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
trevordunham
Helper III
Helper III

Month over Month change by subtraction not percentage

I am looking for a measure to calculate the difference in a field between a month and the prior month by subtraction. The quick measure and everything I can find online shows it as a percentage difference from the prior month.

8 REPLIES 8
TheoC
Super User
Super User

@trevordunham ahh my bad!

 

Month Over Month Difference = 

VAR CurrentMonthValue = 

    CALCULATE (
         MAX ( Table[ValueColumn] ) , 
         LASTDATE ( Table[Date] ) )

VAR PreviousMonthValue = 

    CALCULATE (
         MAX ( Table[ValueColumn] ) , 
         LASTDATE ( Table[Date] , -1, MONTH ) )

RETURN 

CurrentMonthValue - PreviousMonthValue

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@TheoC 

 

The max function appeared to break with this column so I just removed the max function and used the column itself in its place as a troublshooting step. I was returned with the same error:

 

A date column containing duplicate dates was specified in the call to the function LASTDATE

@trevordunham The MAX is important in this instance because it's basically saying, "Let's look at the final value" but because of the duplicate LASTDATE it's triggering the error. 

 

Try this:

 

Month Over Month Difference = 

VAR CurrentMonthEnd = EOMONTH ( MAX ( Table[Date] ) , 0)
VAR PreviousMonthEnd = EOMONTH ( MAX( Table[Date] ) , -1 )
VAR CurrentMonthValue = 
    CALCULATE (
    MAX ( Table[ValueColumn] ) , 
    Table[Date] = CurrentMonthEnd )

VAR PreviousMonthValue = 
    CALCULATE (
    MAX ( Table[ValueColumn] ) , 
    Table[Date] = PreviousMonthEnd )

RETURN 

CurrentMonthValue - PreviousMonthValue

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@TheoC 

 

Still getting the error at the Max function: Column in Table cannot be found or may not be used in this expression

@trevordunham Apologies, mate. Can you provide me with example data?  I think there are going to be too many variables, otherwise. 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

I am not sure of a good way to provide any sample data as this is sensitive data.

TheoC
Super User
Super User

@trevordunham 

 

If I understand correctly, you can you try something like the following measure:

 

Monthly Amount = 

VAR _TotalMth = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Date] )
VAR _TotalLstMth = CALCULATE( SUM ( 'Table'[Amount] , DATEADD ( 'Table'[Date] , -1 , MONTH ) , ALL ( 'Table' ) )

RETURN 

_TotalMth - _TotalLstMth

 

Theo 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@TheoC 

 

This is close but it seems to be erroring out due to the nature of the field I am trying to compare. It is not a sum of a field, but rather a calculated field already of the percentage of a field that are certain responses so the values do not need to be summed or aggregated but are just values themselves.

 

The exact error is: A date column containing duplicate dates was specified in the call. This is not supported

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