Date slicer serving multiple purposes (current month, YTD, Rolling 12-months)

DAX DAX Calculations
1

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)

2

Hi @kkieger ,

To create a rolling 12-month filter column in Power BI, you can follow these steps:

  1. Create a calculated column for rolling 12-month period:
DAXCopy codeRolling 12 Months = IF (    'Calendar'[Date] >= EDATE ( MAX ( 'Calendar'[Date] ), -11 ) &&     'Calendar'[Date] <= MAX ( 'Calendar'[Date] ),    1,    0)
  1. Apply this column as a visual filter:
  • In your visuals, add a visual-level filter using the Rolling 12 Months column and set it to show only values equal to 1.

For more detailed solutions, visit Data Mentor.

Cheers,

Enterprise DNA Support Team