Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I am trying to calculate the last rolling 12 months data that i can put into a line chart visual.
It will need to show monthly going back for 12 months as per the below data. So for Sept 23 it will show the sum of data for the October 22 - Sept 23. August 23 will show data for Sept 22 - Aug 23 and so on.
Total | MonthYear | Rolling 12 months |
40 | 01/01/2022 | |
78 | 01/02/2022 | |
136 | 01/03/2022 | |
275 | 01/04/2022 | |
360 | 01/05/2022 | |
463 | 01/06/2022 | |
522 | 01/07/2022 | |
612 | 01/08/2022 | |
687 | 01/09/2022 | |
761 | 01/10/2022 | |
838 | 01/11/2022 | |
887 | 01/12/2022 | 5659 |
906 | 01/01/2023 | 6525 |
951 | 01/02/2023 | 7398 |
974 | 01/03/2023 | 8236 |
908 | 01/04/2023 | 8869 |
880 | 01/05/2023 | 9389 |
876 | 01/06/2023 | 9802 |
890 | 01/07/2023 | 10170 |
887 | 01/08/2023 | 10445 |
881 | 01/09/2023 | 10639 |
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
A measure like the following might work for you...
rollingTotalMeasure =
var _rollingMonths =
12
var _maxDate =
MAXX(
ALL('Table'),
[MonthYear]
)
Return
CALCULATE(
SUM('Table'[Total]),
FILTER(
ALL('Table'),
[MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths
)
)
Proud to be a Super User! | |
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |