Rolling sum for last 3 months giving same value as current month

[isrark]

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

 CALCULATE (

    SUM ( summaryPriorDataAnalysis[consumption] ),

    DATESINPERIOD ( 'summaryPriorDataAnalysis'[PERIOD], MAX ( summaryPriorDataAnalysis[PERIOD] ), -12, MONTH )

, 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,

[isrark · Accepted 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...

[some_bih]

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

[isrark]

is there any other way to getting rolling sum or rolling average on fact table?

[some_bih]

Hi @isrark  did you try to change part "-12" to "-3"? 

[isrark]

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?