DAX Refer previous calculated value in calculated column formula - puzzle

Question

Is the following possible in DAX?

The following A,B,C,D,E ,..are maturity Buckets.A is a negative bucket , B is positive bucket

Bucket Amount

A -100

B 90

c -200

D 300

E -10

F 30

H -2

The requirement is to calculate the balance for each negative amount.The rule is positive amount can net negative amount in previous bucket , but not the next one. and this positive residual can flow to next available -ve bucket

so the formula for current negative bucket residual = ( previous negative bucket residual) + (SUm of previous positive bucket amounts up to previous negative bucket) + Current negative bucket amount

for A : -100 , the balance is 0 , as there is no positive amount in previous bucket

for C: -200 , the balance = (90 + -200) = -110, as this is a negative number it will not be used for next bucket

for E: -10 , the balance = (0) + 300-10 = 290 as the previous balance in c is negative and this needs to be treated as 0 or ignored.

for H : -2 , the balance = (290) + 30 - 2 = 318

I tried this in DAX using calculated columns using earlier function, but could not able to figure how to look up the previous computed value in the current computation.Its throwing circular dependency errors.

This is an interesting puzzle. I can do this fairly easily with an Excel formula, but I can't figure out how to get around the circular dependency in DAX. My Excel worksheet uses column B for amounts and D for the computations. B1 has a column title and D1 has a zero (0). The amounts begin at B2. I have =IF(IF(D1>0,B2+D1,B2)>0,IF(D1>0,B2+D1,B2),0) in D2, and copied it down the column (of course the cell references adjust during the copy, so in D3 I have =IF(IF(D2>0,B3+D2,B3)>0,IF(D2>0,B3+D2,B3),0). Anyhow...it is interesting. — Marc Pincince

Joe Gravelyn Joe Gravelyn · Accepted Answer · 2017-08-04T22:31:40

Not the ideal of having one additional column, but I was able to generate the expected results using three custom columns. (Note - I used PowerBI for this, so hopefully it's all the same for the Tabular model)

1) Add an index column to the data.

2) Add a column that adds each row's amount to the prior row (if the prior row's amount is positive).

Step1 = IF(Data[Index] = 0, 0,
    Data[Amount] + IF(
        LOOKUPVALUE(Data[Amount], Data[Index], Data[Index] - 1) > 0,
        LOOKUPVALUE(Data[Amount], Data[Index], Data[Index] - 1),
        0
    )
)

3) Add a column that adds each row's amount to the result's of the first formula in the prior row (if the prior row's result is positive)

Step2 = IF(Data[Index] = 0, 0,
    Data[Amount] + IF(
        LOOKUPVALUE(Data[Step1], Data[Index], Data[Index] - 1) > 0,
        LOOKUPVALUE(Data[Step1], Data[Index], Data[Index] - 1),
        0
    )
)

4) Finally, add a column that adds each row's amount to the results of the second formula from the prior row.

FinalResults = IF(Data[Index] = 0, 0, 
    IF(Data[Amount] > 0, 0, Data[Amount] + 
        IF(
            LOOKUPVALUE(Data[Step2], Data[Index], Data[Index] - 1) > 0,
            LOOKUPVALUE(Data[Step2], Data[Index], Data[Index] - 1),
            0
        )
    )
)

I honestly don't know if this method would work for a different set of data, but it works for this one.

Props to Darren Fuller for the idea about using LOOKUPVALUE to get the prior row. Check out his blog post here.

DAX Refer previous calculated value in calculated column formula - puzzle

1 Answers