Method to calculate cumulative negative balances

[dusdau]

Hi Friends,

I have a need to determine the cumulative negative drop in a daily balance.  We have an excel formula that is doing what we want but I'm struggling how to convert this into my power bi data model - I've been trying to use DAX but I am open to using either power query or dax to achieve this.

Here is the excel formula that is working:

Here is  the data model

And my measures:
Net Chg = SUM(CashBookDailySummaryUSD[Trade])

**bleep** Net Chg =

CALCULATE (

        [Net Chg],

        FILTER (

            ALL ( 'Calendar'[Date] ),

            'Calendar'[Date] <= MAX ( ( 'Calendar'[Date] ) )

        )

    )

Cumulative Drop =

VAR NetChg = [Net Chg]

VAR PrevNetChg = CALCULATE([Net Chg], PREVIOUSDAY('Calendar'[Date]))

VAR PrevCumBal = CALCULATE([**bleep** Net Chg], PREVIOUSDAY('Calendar'[Date]))

RETURN

IF(

    NetChg < 0,

    IF(

       PrevNetChg < 0,

       NetChg + PrevNetChg,

       NetChg

    ),

    IF(

       PrevNetChg < 0,

       IF(

          NetChg < ABS(PrevNetChg),

          NetChg + PrevNetChg,

          NetChg

         ),

       NetChg

    )

)

Basically I need to keep accumulating the balance drop into negative until it becomes positive again, and I can't seem to figure out how to do it as I can't reference the previous value of the Cumulative Drop measure.

Thanks in advance for any help.

[v-menakakota]

Hi @dusdau ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @DataNinja777 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you. 

[DataNinja777]

Hi @dusdau ,

the formula you want to create is indeed recursive. A recursive calculation is one where the result for the current row depends directly on the result of the same calculation from the previous row. This self-referential logic is exactly why a standard DAX calculated column fails, as it leads to a "circular dependency" error when the formula tries to reference itself during the computation process.

The most effective and robust way to build this logic in Power BI is by using Power Query, which is designed to handle such sequential, row-by-row operations before the data is loaded into the model. To implement the solution, you will need to add an index column first and then a custom column with the following M code. This code assumes your net change column is named [Net Chg]; please adjust this name in the formula if yours is different.
Of course. Here is the answer rewritten into paragraphs with the code snippet.

Your intuition is correct; the formula you want to create is indeed recursive. A recursive calculation is one where the result for the current row depends directly on the result of the same calculation from the previous row. This self-referential logic is exactly why a standard DAX calculated column fails, as it leads to a "circular dependency" error when the formula tries to reference itself during the computation process.

The most effective and robust way to build this logic in Power BI is by using Power Query, which is designed to handle such sequential, row-by-row operations before the data is loaded into the model. To implement the solution, you will need to add an index column first and then a custom column with the following M code. This code assumes your net change column is named [Net Chg]; please adjust this name in the formula if yours is different.

// This code replicates the recursive Excel logic
// It must be run on a table sorted by date

try
    let
        CurrentNetChg = [Net Chg],
        PrevDrop = #"Changed Type"{[Index]-1}[CumulativeDrop]
    in
        if CurrentNetChg < 0 then
            if PrevDrop < 0 then CurrentNetChg + PrevDrop else CurrentNetChg
        else
            if PrevDrop < 0 then
                if CurrentNetChg < Number.Abs(PrevDrop) then CurrentNetChg + PrevDrop else CurrentNetChg
            else CurrentNetChg
otherwise
    if [Net Chg] < 0 then [Net Chg] else 0

To use this solution, you first need to open the Power Query Editor by clicking "Transform data" in Power BI. It is critical to select your query and sort the table by date in ascending order. After sorting, add an index column by navigating to the "Add Column" tab and selecting "Index Column" (starting from 0). With the index in place, you can then add a "Custom Column" and paste the provided M code into the formula box. The formula uses the index to reference the previously calculated value in the [CumulativeDrop] column, and the try...otherwise block gracefully handles the calculation for the very first row where no previous data exists.

This logic faithfully replicates your Excel formula, where a positive Net Chg is shown once a cumulative drop is offset. Should you prefer the value to reset to zero instead, you can change the final else CurrentNetChg in the code to else 0. While a purely DAX-based solution is technically possible using complex measures with virtual tables, the Power Query approach is far clearer, more maintainable, and generally more performant for this type of recursive task.

Best regards,

[dusdau]

Thanks DataNinja.  I gave this a try today and it does not appear to be referencing the CumulativeDrop column correctly.  I think it might be because the column does not exist in the previous step?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJtisQwCIavUvprCsJEjV9nGeb+11jbSCfd3Q4EfAM+Ud/4eq38xHhSI15hXcIcGHFJXecNewq1Swp12lOWh8b2JxaBHyIMWOwgMABR7mShdKIIrQsE65HWHcTiThbMM0wGqKPGAx3McLvVxfeTdzFQiaI1QJpu/1w8DTE7H5DpAc/+h1XsfImJGXj0E9NLXeM20hhpu4qj3l68QJsGRqbspR9oRpD+RRfvM98pm6pPaaCEd7LgaXVaeh0+uiYW0LDty2U8wJ/FchQgr8UKhMAyeNacP6rIJ45X12Q0mVN0jd/yQS454W75+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"USD Balance" = _t, Change = _t, #"Net Change" = _t, #"Cume Drop Excel" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"USD Balance", type number}, {"Change", type number}, {"Net Change", type number}, {"Cume Drop Excel", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "CumulativeDrop", each // This code replicates the recursive Excel logic
// It must be run on a table sorted by date

try
let
CurrentNetChg = [Net Change],
PrevDrop = #"Added Index"{[Index]-1}[CumulativeDrop]
in
if CurrentNetChg < 0 then
if PrevDrop < 0 then CurrentNetChg + PrevDrop else CurrentNetChg
else
if PrevDrop < 0 then
if CurrentNetChg < Number.Abs(PrevDrop) then CurrentNetChg + PrevDrop else CurrentNetChg
else CurrentNetChg
otherwise
if [Net Change] < 0 then [Net Change] else 0)
in
#"Added Custom"