Periodically aggregated rolling calculation

Power BI
1

Hi All,

I’m working on a visulisation like this:

picture 1_v1
picture 1_v1813×808 37.2 KB

The last column in the graph above (in the current report) shows the December 2023 column for 3 months aggregated from October to December, but I would like to display only December , i.e. the last selected month and every three months (September, June…) from then onwards in the visualisation. I would like to display only the months from the last date selected in the year& month slicer backwards according to the period length, like this:

picture 1_v2
picture 1_v2801×806 137 KB

In the same way, if I filter to a 4 month period, then from the last month backwards, only display the dates every four months in the visualization. The report has a date range filter for the last 13 months on the filter pane.
The rolling period lengths were created using time intelligence calculations. Example of period length calculation for two and three months:

CALCULATE( SELECTEDMEASURE(), DATESINPERIOD(‘DateTable’[Date], LASTNONBLANK(‘DateTable’[Date], SELECTEDMEASURE()), -2, MONTH) )
CALCULATE( SELECTEDMEASURE(), DATESINPERIOD(‘DateTable’[Date], LASTNONBLANK(‘DateTable’[Date], SELECTEDMEASURE()), -3, MONTH) )

I have a fact table and a date dimension table like this:

picture 1_v3
picture 1_v31081×533 44.2 KB

Any help would be greatly appreciated and I look forward to your feedback as soon as possible.

Kind regards

2

Hi @tamas-svaty ,

  1. Create a Measure for the Last Month:
DAXCopy codeLast Selected Month = MAX('DateTable'[Date])
  1. Create a Measure for Period Length (Example for 3 Months):
DAXCopy codeRolling 3 Months = CALCULATE(    SELECTEDMEASURE(),     DATESINPERIOD('DateTable'[Date], [Last Selected Month], -3, MONTH))
  1. Create a Measure for Filtering Specific Months:
DAXCopy codeFiltered Months = IF(    MOD(MONTH('DateTable'[Date]), 3) = 0,     SELECTEDMEASURE(),     BLANK())
  1. Apply the Filtered Measure in the Visualization:
  • Add your Filtered Months measure to the visual.
  • Use the Year & Month slicer to select the desired months.

This approach will show only the last selected month and every third month backward. Adjust the measure for different period lengths as needed. For more detailed guidance, use the Data Mentor site.

Cheers,

Enterprise DNA Support Team