Forum Discussion
Dynamic Rolling 3 Month Sum
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))
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 ) )
2 Replies
- Jihwan_Kim
Super User
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 ) )