Calculate R12M for EACH MONTH, as one measure

[Anonymous]

Hi everyone,

I'm trying to find a way to calculate R12M sum for each of the months in the table, as one single measure. I'd like it to look more or less like this:

Month:	January 2019	February 2019	March	April	May	June	July	August	Sept	Oct	Nov	Dec	Jan 2020	Feb 2020	March 2020
Value:	1	2	3	4	5	1	2	3	4	5	1	2	3	4	5
R12M:												33	35	37	39
														(sum March 2019 - Feb 2020)	(sum April 2019-March 2020)

I found a way to calculate R12M this year and last year, using the formulas below:

RTM (TY) = CALCULATE([Sales Total Sum]; DATESINPERIOD('Calendar'[Date]; MAX('Calendar'[Date]); -12; MONTH))

RTM (LY) = CALCULATE([Sales Total Sum]; DATEADD(DATESINPERIOD('Calendar'[Date]; MAX('Calendar'[Date]); -12; MONTH); -1; YEAR))

However, using this approach I would need to create a separate measure for each month, and it's not what I want.

I'd be grateful for any help 🙂

[v-gizhi-msft]

Hi,

Here is my test table:

Please take following steps:

1)Create a new slicer table by Enter Data:

2)Try this measure:

Measure = 
VAR a =
    IF (
        MAX ( 'Table'[Date] ) >= DATE ( 2019, 12, 1 ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] <= MAX ( 'Table'[Date] )
                    && 'Table'[Date]
                        >= IF (
                            MONTH ( MAX ( 'Table'[Date] ) ) = 12,
                            DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, 1, 1 ),
                            DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, MONTH ( MAX ( 'Table'[Date] ) ) + 1, 1 )
                        )
            )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[Month] ),
        "Value", SUM ( 'Table'[Value] ),
        "R12M", a
    )

3)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[parry2k]

@Anonymous you don't need to create two measure, put year and month from calendar table and one measure would work?

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Could you show me how such a measure would look like?

[parry2k · Accepted solution]

@Anonymous your measure RTM (TY)  should work? If not, share what is not working.

[Anonymous]

Hi, wasn't aware I can simply plot it on a timeline and it will calculate. For some reason I was convinced that I have to use some kind of variable to select a specific month, becasue I thought it will always take the whole calendar table (without filtering) as a basis. So easy! Thank you.