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
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 🙂
Solved! Go to 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.
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
@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.
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.
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.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |