Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 🙂

 

1 ACCEPTED SOLUTION

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
v-gizhi-msft
Community Support
Community Support

Hi,

 

Here is my test table:

6.PNG

Please take following steps:

1)Create a new slicer table by Enter Data:

8.PNG

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:

10.PNG

See my attached pbix file.

 

Best Regards,

Giotto

parry2k
Super User
Super User

@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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.