Periodically aggregated rolling calculation

[svttm94]

Hi All,

I'm working on a visulisation like this:

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:

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:

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

Kind regards

[v-huijiey-msft · Accepted solution]

Hi @svttm94 ,

I created sample data as shown in the table below:

Date	Sales
1/1/2023	100
2/1/2023	200
3/1/2023	150
4/1/2023	250
5/1/2023	200
6/1/2023	450
7/1/2023	400
8/1/2023	300
9/1/2023	350
10/1/2023	600
11/1/2023	650
12/1/2023	750
1/1/2024	700

An index column starting from 1 is created in Power Query.

Created a calculated column for year and month:

Year & Month = YEAR('Table'[Date]) & " " & 'Table'[Date].[Month]

A new table is created containing 1-12.

Do not establish a relationship between the two tables.

Put "Year and Month" and "Period" into the slicer.

Create two measures:

Measure = MAX('Table (2)'[period])

Measure2 = 
IF (
    ROUND (
        DATEDIFF (
            MAX ( 'Table'[Date] ),
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
            MONTH
        ) / [Measure],
        2
    )
        - ROUND (
            DATEDIFF (
                MAX ( 'Table'[Date] ),
                MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
                MONTH
            ) / [Measure],
            0
        ) = 0,
    1
)

Using the "Clusterd column chart" visual, drag the fields into position.

The Index column is for better sorting.

Drag Measure2 into Filter and set the filter condition to is 1.

Select the year and month from March to December 2023. The visual effect of selecting Period 3 is as follows:

Select the year and month from March to December 2023, and the visual effect of selecting Period 4 is as follows:

The pbix file has been attached, if you have any other questions please feel free to contact me.

If this method does not solve your needs, maybe you can try uploading your pbix file to me. Please be careful not to log in to your account in Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[svttm94 · Accepted solution]

Hi @v-huijiey-msft 

Sorry for the late reply. I could use your solution, thank you, but I had to modify my measure calculation.
I had to move the time intelligence calculation items created in the tabular editor to a measure and refer to Table (2)'[period] as follows:

Market Share (UC)  = CALCULATE(
    [Market Share (UC)],
    DATESINPERIOD('DateTable'[Date], LASTNONBLANK('DateTable'[Date],  [Market Share (UC)]), - MAX('Table (2)'[period]), MONTH)
)

Using this calculation and your Measure and Measure 2 calculations, the filtering of the visualization by period now works correctly.

Thanks again for your help!

[v-huijiey-msft · Accepted solution]

Hi @svttm94 ,

I created sample data as shown in the table below:

Date	Sales
1/1/2023	100
2/1/2023	200
3/1/2023	150
4/1/2023	250
5/1/2023	200
6/1/2023	450
7/1/2023	400
8/1/2023	300
9/1/2023	350
10/1/2023	600
11/1/2023	650
12/1/2023	750
1/1/2024	700

An index column starting from 1 is created in Power Query.

Created a calculated column for year and month:

Year & Month = YEAR('Table'[Date]) & " " & 'Table'[Date].[Month]

A new table is created containing 1-12.

Do not establish a relationship between the two tables.

Put "Year and Month" and "Period" into the slicer.

Create two measures:

Measure = MAX('Table (2)'[period])

Measure2 = 
IF (
    ROUND (
        DATEDIFF (
            MAX ( 'Table'[Date] ),
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
            MONTH
        ) / [Measure],
        2
    )
        - ROUND (
            DATEDIFF (
                MAX ( 'Table'[Date] ),
                MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
                MONTH
            ) / [Measure],
            0
        ) = 0,
    1
)

Using the "Clusterd column chart" visual, drag the fields into position.

The Index column is for better sorting.

Drag Measure2 into Filter and set the filter condition to is 1.

Select the year and month from March to December 2023. The visual effect of selecting Period 3 is as follows:

Select the year and month from March to December 2023, and the visual effect of selecting Period 4 is as follows:

The pbix file has been attached, if you have any other questions please feel free to contact me.

If this method does not solve your needs, maybe you can try uploading your pbix file to me. Please be careful not to log in to your account in Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!