cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 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 🙂

1 ACCEPTED SOLUTION
Super User

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

5 REPLIES 5
Community Support

Hi,

Here is my test table:

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

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?

Super User

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