Forum Discussion
Periodically aggregated rolling calculation
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
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 TeamIf 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!Hi Anonymous
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!
2 Replies
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 TeamIf 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!Hi Anonymous
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!