Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi I'm trying to achieve rolling sum for last 3 months from my current record. I have sample data like below
State | Town | Block | Period | Consumption
A | a1 | ab1 | 10/1/2022 | 100
A | a1 | ab1 | 11/1/2022 | 102
A | a1 | ab1 | 12/1/2022 | 150
A | a1 | ab2 | 11/1/2022 | 101
B | b1 | bb1 | 12/1/2022 | 103
B | b2 | bb3 | 9/1/2022 | 110
for one particular block, i need to calculate last 3 months rolling consumption or rolling avg consumption
However when i used this formula
, i get same value as current consumption record. Please help?
For each record i need to get it's three months consumption sum.
NOTE: the period column is not unique,
Solved! Go to Solution.
I found a solution to my problem,
1.I created a calender series table first which has start of the month dates only. added relation to the fact table
2.I created a measure for actual sum value
3.Then using a measure i computed sum of 3 months including current month
4.Then using a measure i got the previous month value from measure (point 3)
Reference : https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/dax-for-rolling-average-of-last-3-mo...
I found a solution to my problem,
1.I created a calender series table first which has start of the month dates only. added relation to the fact table
2.I created a measure for actual sum value
3.Then using a measure i computed sum of 3 months including current month
4.Then using a measure i got the previous month value from measure (point 3)
Reference : https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/dax-for-rolling-average-of-last-3-mo...
Hi @isrark I overlook your part DATESINPERIOD as whole, just -12 part, sorry :). DATESINPERIOD is time intelligencee function and the best work with Date / Calendar table. Please adjust part of your measure DATESINPERIOD to include not fact table, rather Date / Calendar table and see results. Hope this help
Proud to be a Super User!
is there any other way to getting rolling sum or rolling average on fact table?
Hi @isrark did you try to change part "-12" to "-3"?
Proud to be a Super User!
yes, i'm still getting same result as consumption record. is there any way to provide filter to the query to select records as per block - town - state combination and check for sum of last 3 months?
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |