Forum Discussion
12 month rolling average DAX
Hello, I haven't found a solution that works for my project so I am hoping someone could help identify where it went wrong and how I can fix it.
I am trying to do a 12 month rolling average on the Month over Month % change. The data starts in May 2022 and last till today.
As you can see, the 12 month rolling % is not correct and for the month <= April 2023, the 12 month rolling should be the same as YTD% as the # of month is less than 12 months.
And from May 2023, it should be calculating the MoMVar% from May 2023 back to June 2022. I do have a date table which has a 1:many relationship with my value table on billing date.
here is the sample data:
| Year | Month | Sum of _EffChargeBaseGross | _EffChargePBC | _MoMVariance | _MoMVar% | _YTD% | _12moRolling% |
| 2022 | May | $17,885.07 | $17,885.07 | ||||
| 2022 | June | $146,414.81 | $17,885.07 | $128,529.74 | 718.64% | 718.64% | -72.71% |
| 2022 | July | $142,042.75 | $146,414.81 | ($4,372.06) | -2.99% | 357.83% | -84.99% |
| 2022 | August | $212,896.38 | $142,042.75 | $70,853.63 | 49.88% | 255.18% | -88.42% |
| 2022 | September | $207,696.94 | $212,896.38 | ($5,199.44) | -2.44% | 190.77% | -90.47% |
| 2022 | October | $174,485.42 | $207,696.94 | ($33,211.52) | -15.99% | 149.42% | -91.85% |
| 2022 | November | $201,927.65 | $174,485.42 | $27,442.23 | 15.73% | 127.14% | -92.55% |
| 2022 | December | $229,143.44 | $201,927.65 | $27,215.79 | 13.48% | 110.90% | -93.10% |
| 2023 | January | $192,524.95 | $229,143.44 | ($36,618.49) | -15.98% | -15.98% | -93.63% |
| 2023 | February | $216,842.11 | $192,524.95 | $24,317.16 | 12.63% | -1.67% | -93.87% |
| 2023 | March | $211,785.10 | $216,842.11 | ($5,057.01) | -2.33% | -1.89% | -94.18% |
| 2023 | April | $305,750.28 | $211,785.10 | $93,965.18 | 44.37% | 9.67% | -94.27% |
| 2023 | May | $299,452.09 | $305,750.28 | ($6,298.19) | -2.06% | 7.33% | -94.49% |
| 2023 | June | $276,445.76 | $299,452.09 | ($23,006.33) | -7.68% | 4.82% | -96.48% |
| 2023 | July | $281,506.00 | $276,445.76 | $5,060.24 | 1.83% | 4.40% | -96.46% |
| 2023 | August | $329,099.99 | $281,506.00 | $47,593.99 | 16.91% | 5.96% | -96.55% |
| 2023 | September | $364,664.69 | $329,099.99 | $35,564.70 | 10.81% | 6.50% | -96.52% |
| 2023 | October | $386,987.24 | $364,664.69 | $22,322.55 | 6.12% | 6.46% | -96.46% |
| 2023 | November | $534,173.01 | $386,987.24 | $147,185.77 | 38.03% | 9.33% | -96.39% |
| 2023 | December | $95,777.32 | $534,173.01 | ($438,395.69) | -82.07% | 1.71% | -96.66% |
Any help is much appreciate. thank you!
3 Replies
- littlemojopuppy
Community Champion
Thanks! I have read this post before but I don't know how I missed the part where using 'Date Table'[Date] within the Calculate function. Thanks for validating the approach to make me looking at it more closely.
- littlemojopuppy
Hi shiex1 glad I could help! 🙂
