Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
23 | |
14 | |
11 |
User | Count |
---|---|
78 | |
63 | |
46 | |
17 | |
12 |