Calculating Month on Month Change based on the last aggregations of the current month - previous mon

[afaro]

I have data like this: 

Date	ID	Value
1 Jan 2020	A	20
1 Jan 2020	B	30
10 Jan 2020	A	40
10 Jan 2020	B	60
31 Jan 2020	A	70
31 Jan 2020	B	80
1 Feb 2020	A	100
1 Feb 2020	B	120
28 Feb 2020	A	300
28 Feb 2020	B	100
28 Feb 2020	C	200
31 March 2020	F	600

First of all, I have data for the different IDs for every single day in the year for many years. (I haven't shown in the table all the days in January or February but I have data for that)

From this data I want to find out 4 changes: Day on Day, Month on Month, Quarter on Quarter, Year on Year 

The day on day change DAX is simple and I have figured that out. 

For Month on Month Change, I want the change for month of February to be (300+100+200)-(70+80) = 450 
For March, it would be (600) - (300+100+200) = 0 

Which is basically taking the aggregate on the last date of that month and subtracting it from the aggregate of the last date of the previous month. If it is the current date, then the last date of the month would be current date. 

I prefer not to to create separate summarize tables for this and would prefer to write measures that help me do this. 

@Anonymous @Anonymous @Anonymous @Anonymous @Anonymous 

[Anonymous · Accepted solution]

Hi  @afaro ,

You can try formula like below:

MEASURE =
VAR cur_ =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
                && DAY ( 'Table'[Date] ) = DAY ( ENDOFMONTH ( 'Table'[Date] ) )
        )
    )
VAR pre_ =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            MONTH ( 'Table'[Date] )
                = MONTH ( MAX ( 'Table'[Date] ) ) - 1
                && DAY ( 'Table'[Date] ) = DAY ( ENDOFMONTH ( 'Table'[Date] ) )
        )
    )
RETURN
    IF ( MONTH ( MAX ( 'Table'[Date] ) ) <= 1, BLANK (), cur_ - pre_ )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.