Month over Month change by subtraction not percentage

[trevordunham]

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.

[TheoC]

@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

[trevordunham]

@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

[TheoC]

@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

[trevordunham]

@TheoC 

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

[TheoC]

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

[trevordunham]

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

[TheoC]

@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 

[trevordunham]

@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