Forum Discussion
Rolling sum for last 3 months giving same value as current month
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,
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)
5 Replies
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)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?
- some_bih
Community Champion
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
is there any other way to getting rolling sum or rolling average on fact table?
