Running total by reset condition - DAX in Power BI

In this writing, I want to share my thought process about how I build it up to provide a solution for showing running total that is reset by a certain condition. In the previous writing, I shared a running total solution that is created in Power Query Editor, including running total as group.

In my opinion, writing DAX can provide more options and more dynamic ways to get to the solution, especially when the requirement is to reset running total by a certain condition. For instance, MTD, QTD, or YTD is also a kind of resetting running total every first date of month, quarter, or year.

In this blog post, I want to share how my thought process flows when the requirement is, reset it every time when running total becomes near to a hundred (or, slightly over a hundred). I think, even in the other conditions, if it is not related to recursive types of conditions, I may have a similar thought process.

Let's start.

The below screen shot is the simple sample that I created for this blog post.

And, the following, that is described in DAX Studio, is showing how my thought process flows.

Step 1: [@RunningTotal] -> It is just a simple running total.

Step 2: [@HundredFlag] -> Divide a running total value by 100, and round it down to only show "Whole Number" part.

Step 3: [@PrevRow] -> To show a result of [@HundredFlag] of the previous row.

Step 4: [@changeconditionflag] -> If [@HundredFlag] of previous row and current row are different, it means that the status is changed. This is considered as a reset row. If it is a reset row, thes show 1, else show 0.

Step 5: [@cumulategroup] -> Calculating a cumulate sum of [@changeconditionflag] shows which rows fall into the same group.

Note: in Step 2, [@HundredFlag] already shows Group, and from Step 2, you can directly go to Step 6. However, I just wanted to add a few more steps to provide how we can think differently if the required condition is totally different and we need to find status-changing-rows, for instance, if the requirement is, reset every time when a value is lower than a previous date.

Step 6: [@resultcumulate] -> Based on considering the same group that is defined in Step 5, it shows the final result.

The below is what I wrote in DAX Studio.

There are other ways to get this result.

One of ways is to write 5 supporting measures to get to the result. And, in my opinion, writing 5 supporting measures is easier to get to the correct result and understand than writing one-long-measure. In this blog post, I wanted to show my thought process from step one to step six in one screen, so I decided to write one-long-DAX-formula.

I hope this helps readers to have more fun in thinking how to write DAX when there are certain conditions to be considered.