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.

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

``````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!

Helper III

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

Super User

@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!

Helper III

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

Super User

@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!

Helper III

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

Super User

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!

Helper III

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

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