Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate R12M for EACH MONTH, as one measure

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 2019February 2019MarchAprilMayJuneJulyAugustSeptOctNovDecJan 2020Feb 2020March 2020
Value:123451234512345
R12M:           33353739
              (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 🙂

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

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

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

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

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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