Running Balance DAX Formula

[ronaldbalza2023]

Hello everyone,

I hope you're doing well. I have a question regarding creating a running balance DAX formula using a specific data table.

I'm working with a data table that includes columns for Date, Transaction Type (debit or credit), and Amount. My goal is to calculate a running balance based on these transactions. Essentially, I want to derive a cumulative balance that updates with each new transaction, adjusting based on whether it's a debit or credit.

Thank you in advance for any help you can provide.

[danextian · Accepted solution]

Hi @ronaldbalza2023 

Create a dates table, relate that to your fact table and create this sample measure.

Cumulative Total Revenue = 
CALCULATE (
    [Total Revenue],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

Please see attached pbix for details.

[danextian · Accepted solution]

hi @ronaldbalza2023 ,

Did you use a separate dates table as suggested? On top of that, you must use the date column from that dates table as the filter modifier ,  ALL (  Dates ), is applied to that table.

[danextian · Accepted solution]

Hi @ronaldbalza2023 

Create a dates table, relate that to your fact table and create this sample measure.

Cumulative Total Revenue = 
CALCULATE (
    [Total Revenue],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

Please see attached pbix for details.