Forum Discussion

Anton0315's avatar
Anton0315
Frequent Visitor
2 years ago

Running Total reset by own value

I want for the month when the cumulative sum first exceeds 3000, to show the remaining private km above 3000, and for subsequent months after the reset, to show the normal sum of private km.

I'm using this Dax formula:
Cumulative_Private_KM =  
VAR CmulativesSum = IF(SUM(data[Private KM] <> blank() , 
                                           CALVULATE(SUM(date[Private KM]) , 'Calendar' [date] <= max('Calendar' [date])
                                     RETURN 
                                               IF(CumulativeSum > 3000, sum(data[Private KM] , CumulativeSum)


Is this possible or i just imagine it is because i have tried several functions and it is not working.
 
 
Thank you in Advance!!!


Need Help
Show and Tell

2 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi Anton0315 ,


    Can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Best Regards,
    Yulia Yan

    • Anton0315's avatar
      Anton0315
      Frequent Visitor
      2 years ago

      Hello v-weiyan1-msft

      So what i want is okay because i have restarted the running total.
      But for August month it is a wrong result because this is the month it reaches 3000 km 

      What i want only for the month wich reaches the 3000km is to show not 919 because this is the private km for the month but the remaining km of the 3000 so it should be 477. For september and october the private km are right I want to keep the values there

      Thank you in advance