Forum Discussion

WaXpEd's avatar
WaXpEd
Frequent Visitor
5 years ago

Cumulative Total with Reset on Negative amounts

Hi the PB community,

 

I think one picture will summarize better than words.

 

I try to have the display of our UI based on a credits adding / removing. (but sometimes there is some bugs).
Once the cumulative count is at 0 or negative, the formula should display 0, otherwise, the amount of credits.
But once we get credits back, I cant reset the count, it will sum the last values.

I have the overall formula with EARLIER but can't find a way to make him understand to take only credits after the last adding without negative values before.

In EXCEL, formulas is :
=IF(SUMIFS($W$1:W3;$C$1:C3;C4)+V4>0;V4;0)

 

 

 

Thanks in advance to any helper 🙂

 

 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    WaXpEd 

     

    It would really be more informative if you showed what you want to do in PBI instead of Excel. From the above I honestly can't make out how your description fits in with the picture (but it may just be me). Can you please give a true picture of what you want to do? You can create a small simplified model in which you'll demonstrate exactly what it is you're trying to achive... It will then be easier for any one of us here to help you. If you want to know how to make a good post that should get you correct answers, please read this: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-p/1626726

     

    Thanks.

    • WaXpEd's avatar
      WaXpEd
      Frequent Visitor
      5 years ago

      Hi Anonymous,

       

      Thanks for taking the time to answer me.

      As the picture below :

      • The file contain a lot of UserIDs
      • The balance of credits is already calculated
      • Current Result : what i've done so far, without the reset

      Desired Result :
      We need to know what credits are added/removed based on the balance.

      We have a glitch in our UI that not remove credits if the balance go <0.

      Based on this we do not take the values when the balance go under 0.

      But once the client buy credits, we need to reset the balance with the amount of credits purchased without considering the negative balance on the previous lines.

       

      Don't know how to up the pbix, but here is a picture :

       

       

      Hope this helps,

      If not, will try my best but...

       

      Thanks anyway for yout time,

      WaX.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    WaXpEd 

     

    I don't think you should do it in DAX, even though I know it's possible since I've done such things is the past. The easiest way to get what you want is to use Power Query where you can easily make use of recursion. Have you tried Power Query?

    • WaXpEd's avatar
      WaXpEd
      Frequent Visitor
      5 years ago

      Anonymous 

      Quite new in DAX and definitely novice on PowerQuery.
      I guess i'll go with Excel then even if the calculation duration is massive.

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        WaXpEd 

         

        My friend, you are giving up too early... Power Query is a very easy thing to use. Just a little bit of Googling and YT'ing and you can get 99% things done. Do not give up so easily. Also, there is another forum where people can give you help on issues related to Power Query. I would certainly post your question (re-phrasing a bit) on there.

         

        https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services