Hello,
Could you please advise how to create rolling time period groupings in Power BI? Monthly files are added to a folder each month. From there I need to add MAT (moving annual total for current and previous year) and rolling QTR groupings, based on the latest data file.
In the attached example, the latest data is for Nov-2023. Would need to create period groupings as below. Which would then need to roll over each month accordingly, when the new data file is added.
| Moving Annual Total | |
| MAT2023 | Dec-22 to Nov-23 |
| MAT2022 | Dec-21 to Nov-22 |
| Rolling Quarters | |
| Q4-23 | Sep-23 to Nov-23 |
| Q3-23 | Jun-23 to Aug-23 |
| Q2-23 | Mar-23 to May-23 |
| Q1-23 | Dec-22 to Feb-23 |
| Q4-22 | Sep-22 to Nov-22 |
| Q3-22 | Jun-22 to Aug-22 |
| Q2-22 | Mar-22 to May-22 |
| Q1-22 | Dec-21 to Feb-22 |
Hi Danielle,
Rolling 3, or 12 months should be easy:
Rolling3Month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
If you want to create custom date buckets, you should do that in your date table, based on the MAX date from your data.
If you don't have a calendar table, you should create one, this is where the calculated groups should be.
Thanks, Catalin.
I attempted to use the above formula, but am missing something. The message I receive is: "Failed to resolve name 'MONTH'. It is not a valid table, variable, or function name.'
I do have a calendar table that includes a MONTH column. Am not sure where I'm going wrong. Please see attached.
Could you please advise?
Many thanks.
I believe you made a small typo when you wrote the measure.
=CALCULATE(SUM(Units[Units]),DATESINPERIOD('Calendar'[Date],MAX(Units[Date])-3,MONTH))
there is a comma missing.
=CALCULATE(SUM(Units[Units]),DATESINPERIOD('Calendar'[Date],MAX(Units[Date]),-3,MONTH))
Thanks, Riny.
This calculation works now.
It shows the months for every rolling quarter. I only want to show every 3 months on the chart for the last 15 months. Is there a way to filter this?
I tried another way to do this in Power BI, via Date Bins. But it uses the month name for every quarter (e.g. October 2022, January 2023, April 2023, July 2023, October 2023); I'd prefer to have these automatically renamed as Q4 2022, Q1 2023, Q2 2023, Q3 2023, Q4 2023) with each month's data refresh using the last 15 months. Also, the 3 month date bins start with Oct22 - Dec22, ending at Oct23 - Nov23. This should actually start with Sep22 - Nov22 and end with Sep23 - Nov23. And then when the Dec23 data is available, should change to new 3 month date bins for the last 15 months.
Is there a way to do this?
Please see attached example.
Many thanks.
Don't really understand what you want to achieve. Quite difficult to see what's going in your model based on screenshots alone.
Hi Riny,
I've added charts to the attachment of what I need to create in in Power BI.
Hopefully this example makes it clearer?
I've added two calculated columns that determine the Quarterly groupings. One for the regular calendar quarter and one for the quarter that is shifted by one month. In the chart sheet you'll find two pivot tables, each grouped by the relevant quarter type.
I believe this does what you asked for.
Thank-you, Riny.
This sounds like the solution I'm looking for. Although I'm unable to view the pivot table when I open the attachment. Are you able to please share the power piviot calculations in a message so I can see what calculations I need to use?
Excel message:
"Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable3.xml part (Data store)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable3.xml part (PivotTable view)"
How strange. Received the same messages when I opened my own file. Saved it under a different name and then I could open it without errors. All seems to work fine on my side. Please try again.
