Running totals with if and max value DAX

Question

I want to create a table about leave and need a cummulative total.

The Annual Leave Column2 DAX is

Annual Leave Column2 = 
CALCULATE (
    SUM (Sheet1[Debit/Credit]),
    ALL ( Sheet1 ),
    FILTER(Sheet1, SUM(Sheet1[Debit/Credit])>20), Sheet1[Date] <= EARLIER ( Sheet1[Date] )
) 

and for the column 3 is

column 3 = IF( Sheet1[Annual Leave Column2]>20, 20, Sheet1[Annual Leave Column2] )

But the result when it already 20 and there is a -1 in next date it will still count 20 and stuck in 20. And the result that I need is:

I just think, Can we stop the calculation if the value already 20? and continue the calculation if met -1. Or there is another way how to do it? It's looks like if I using IF it's just make the visualization into 20 but not set the data into 20, that's why it's stuck in 20 because all the sum is more than 20.

Answer 1

Your best bet is for your source system to either not allow annual credits that exceed the annual cap, or to provide you with the capped annual leave amount directly (rather than trying to calculate it in Power BI).

Even if the source system doesn't store the capped annual leave amount, it may be easier to calculate it in a query using SQL than using Power BI. (I'd recommend a separate question for this.)

Why Do I Say This?

In Excel, a running (or cumulative) total is calculated row-by-row based on the value from the previous row. This makes it easy to "override" the running total and have that override apply to each subsequent row. E.g. you can cap the running total at 20, and the cell underneath acts as if the running total is 20. The cells underneath the cap have no idea that the running total is not really 20.

In DAX, the running total is calculated independently on each row (meaning each row looks at all the rows prior to the current rows date and calculates what the actual running total is). This makes it impossible to override the running total (e.g. by capping it at 20) and have that adjusted running total feed into the next row. The next row always knows what the real running total is.

There's no way to tell DAX to look at the previously calculated running total and add to it because a column cannot reference itself (as user5226582 mentioned, it's a circular dependency). DAX, unlike Excel, runs calculations column by column rather than cell by cell, so it can't use the output of a column as an input for the same column.

Dirty & Incomplete Workaround

A dirty workaround would depend on how many times Annual Credits can get disregarded. Each time all or part of an annual credit is disregarded, it adjusts the running total for all subsequent cells.

For example, on 1-May-17, the true running total is 20.5, but you throw away the 0.5. This means all future rows are based on a running total of 20 on 1-May-17, not 20.5.

You could certainly create a calculated column that identifies the first time the running total is capped (1-May-2017). You would then calculate an Adjusted Running Total that uses the previously calculated running total prior to 1-May-17, but after 1-May-17 ignores the prior running total and instead sums the [Debit/Credit] column from 1-May-17 only plus 20. (Adding 20 because we know the running total is at 20 on 1-May-17 and that won't be reflected in the sum of the [Debit/Credit] column.)

Running Total Is Capped = IF([Annual Leave Column2] > 20, 1, 0)

Running Count of Capped =
CALCULATE (
    SUM ( Sheet1[Running Total Is Capped] ),
    ALL ( Sheet1 ),
    FILTER ( Sheet1, Sheet1[Date] <= EARLIER ( Sheet1[Date] ) )
)

Adjusted Running Total =
IF (
    [Running Count of Capped] = 0,
    [Annual Leave Column2],
    20
        + CALCULATE (
            SUM ( Sheet1[Debit/Credit] ),
            ALL ( Sheet1 ),
            FILTER (
                Sheet1,
                Sheet1[Date] <= EARLIER ( Sheet1[Date] )
                    && Sheet1[Running Count of Capped] > 1
            )
        )
)

This solution doesn't hold up though because it only works for the first time the cap is hit. Each time you hit the cap, you'll need to adjust the running total in the same way, with a new set of calculated columns that adjusts the adjusted running total. If you can hit the cap 20 times, or 50 times, you'll need the above set of calculated columns repeated 20 or 50 times too.

You can't adjust for the cap across all rows simultaneously, because the first adjustment affects when the next adjustment happens. In your example data, the true running total on 5-Aug-17 is 21, meaning we'd want to reduce it to 20. However, because we've gone over the cap 3 times before, we've already shaved 3.5 days from the running total as a result, and so the adjusted running total is 17.5 and therefore doesn't need capping.

Apart from the sheer number of calculated columns you'd need, the model would also not hold up well to increased volume of data. The EARLIER function is iterative, meaning it runs its calculation once for every row. The more rows, the longer it takes. Using the EARLIER function over & over again as this quick & dirty workaround does would be a performance killer. I strongly recommend finding another solution, ideally before the data reaches Power BI.

Sidenote: If you're going to use the EARLIER function, I'd recommend indexing each row so they're guaranteed to have a unique number rather than relying on the date field as an index. Using the date field as an index could cause unexpected results if you have multiple credits/debits on the same date.