Forum Discussion
Rolling 12 months DAX calculation
Hello,
Good Day!
I have a requirement to visualize rolling 12 months(last 12 monts from selected in filter) data in bar graph. Please give me a suggetion how can I acheive DAX for that.
Please note that, system recognized dates are not available in my data.
Here is the sample data:
| Year | Month | Value |
| 2022 | 1 | 100 |
| 2022 | 2 | 101 |
| 2022 | 3 | 102 |
| 2022 | 4 | 103 |
| 2022 | 5 | 104 |
| 2022 | 6 | 105 |
| 2022 | 7 | 106 |
| 2022 | 8 | 107 |
| 2022 | 9 | 108 |
| 2022 | 10 | 109 |
| 2022 | 11 | 110 |
| 2022 | 12 | 111 |
| 2023 | 1 | 112 |
| 2023 | 2 | 113 |
| 2023 | 3 | 114 |
| 2023 | 4 | 115 |
| 2023 | 5 | 116 |
| 2023 | 6 | 117 |
| 2023 | 7 | 118 |
| 2023 | 8 | 119 |
| 2023 | 9 | 120 |
| 2023 | 10 | 121 |
| 2023 | 11 | 122 |
| 2023 | 12 | 123 |
| 2024 | 1 | 124 |
| 2024 | 2 | 125 |
| 2024 | 3 | 126 |
| 2024 | 4 | 127 |
| 2024 | 5 | 128 |
| 2024 | 6 | 129 |
| 2024 | 7 | 130 |
| 2024 | 8 | 131 |
| 2024 | 9 | 132 |
| 2024 | 10 | 133 |
| 2024 | 11 | 134 |
| 2024 | 12 | 135 |
Hi sivaadityat
You can refer to the following solution.
1.Create a slicer table
Slicer = SUMMARIZE('Table',[Year],'Table'[Month])2.Create a measure
Measure = VAR a = DATE ( MAX ( 'Slicer'[Year] ), MAX ( 'Slicer'[Month] ), 1 ) VAR b = EOMONTH ( a, -12 ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', EOMONTH ( DATE ( MAX ( 'Table'[Year] ), MAX ( 'Table'[Month] ), 1 ), 0 ) >= b && EOMONTH ( DATE ( MAX ( 'Table'[Year] ), MAX ( 'Table'[Month] ), 1 ), 0 ) < EOMONTH ( a, 0 ) ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ashish_Mathur
Super User
PBI file attached.
- lbendlin
Your sample data is not usable.
You also need to define what you mean by "last 12 months" - does that include the currently selected month?
Hi sivaadityat
You can refer to the following solution.
1.Create a slicer table
Slicer = SUMMARIZE('Table',[Year],'Table'[Month])2.Create a measure
Measure = VAR a = DATE ( MAX ( 'Slicer'[Year] ), MAX ( 'Slicer'[Month] ), 1 ) VAR b = EOMONTH ( a, -12 ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', EOMONTH ( DATE ( MAX ( 'Table'[Year] ), MAX ( 'Table'[Month] ), 1 ), 0 ) >= b && EOMONTH ( DATE ( MAX ( 'Table'[Year] ), MAX ( 'Table'[Month] ), 1 ), 0 ) < EOMONTH ( a, 0 ) ) )Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
