Hi,
I think this will be fun to solve. I have monthly Financial period table and a typical Calendar Date table which join. In my attached pbix, I have simple transaction tables which correlate to their respective Calendar Date (Sales) and Period (Margin) table.
All of my measures to produce YTD totals or Average per month for the selected period, or rolling 12-month totals are working based on a monthly period slicer and a USERELATIONSHIP shadow period table. All calculated in the pbix.
The problem is with the monthly chart shown. I’m looking for a filter to reflect the current period (month) plus the previous 11 months (i.e. rolling year) based on the selected slicer value. I’m not quite clear in how to create the filter column - rather than measure - to display the 12 trending months from the selected period value and apply it properly to the USERELATIONSHIP measures.
Please give it a look as to the “Rolling 12 months filter” column which should work for both Revenue [by Date] and Margin [by Period] measures.
Thank you,
Kevin
Selected Period YTD with Averages.pbix (152.5 KB)
eDNA Sales.xlsx (53.3 KB)