The formula was shared by @v-zhangti in my previous post
The formula seems to be working fine however I would like the running total of 9 months to reset at 2022-01 as it hits 0 and then only cumulate afterwards upto 9 Months and not from the begining of the data.
Hi @yingyinr , Thanks for sharing your solution. I tried following the DAX approach as it fits more to my problem however despite getting all the variant correctly(can be seen in pic below), the sum isnt reflecting what is expected. In April 2022, it should have cumulated value from Feb 22 to April 22.
Is there anything I'm missing
Conditional **bleep** =
VAR _currentValue = [LAST 4 MONTHS]
VAR _currentmonth = MAX('Date'[Month Year])
VAR _sum = SUMX(FILTER('Date', 'Date'[Month Year]<= _currentmonth), [Monthly Value])
Hi @lbendlin , Appreciate your response, in my scenario the score needs to cumulate for 9 months irrespective of the calendar year. However it also needs to reset to 0 once there's 0 score in consecutive 4 Months(including current Month) which happened in month of January 2022.