Dynamic Rolling 3 Month Sum

[Xboraxe]

Hi all,

Having the below table, I want to be able to filter by month and have the 3 month rolling sum automatically update as a card visual.

month	lead_cnt	total_3month
202312	0	600
202311	100	800
202310	500	900
202309	200	700
202308	200	600
202307	300	400
202306	100	100

I'm currently using the below DAX but for some reason the sum is only accurate when I filter for October (202310). When I filter for other months the card doesn't automatically sum the respective previous 3 months. For instance, 202311 shows 100 and for 202312 shows 0, both incorrect.

total_3month = 
CALCULATE(
    SUM(table[lead_cnt]),
    DATESINPERIOD(date_table[mmm],LASTDATE(date_table[mmm]),-3,month))    

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

total 3 months measure: = 
CALCULATE (
    [lead cnt measure:],
    DATESINPERIOD ( date_table[Date], LASTDATE ( date_table[Date] ), -3, MONTH )
)

[Xboraxe]

Thank you, this is exactly what I needed.