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.

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

@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

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!

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

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!

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

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!

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

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

## Helpful resources

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