adding running total to a single balance value table

[Pmorg-73]

This would be easy in Excel. And I have not been using PBI for the last year so a bit rusty, but had previously used it quite a bit.

I have a table of credit card transactions with dates

I have then got a single row table with a balance $#### on 04/05/2022

What is the best approach for making a new running total that takes the original balance and goes to the other table of transactions and adding/subtracting the transactions?

I do already have a sum[transactions] measure

and also a RT transactions measure

Credit Card RT =

VAR _Lastdate = max('01_Calendar'[Date] )

VAR _firstdate = MIN('01_Calendar'[Date])

VAR _Lastdatewithinvoice = CALCULATE(

    MAX('Credit Card Transactions'[Transaction Date]  ),

    REMOVEFILTERS()

)

VAR Result =

    IF(_firstdate <= _Lastdatewithinvoice,

    CALCULATE(

        [Sum Credit Card],

            '01_Calendar'[Date] <= _Lastdate,

            REMOVEFILTERS('01_Calendar'[Date])

            )

        )

return

Result

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution in the attached files help.

[amitchandak]

@Pmorg-73 , The table having original balance, you use it without joining to date table or use removefilters/all  

= calculate(sum(Table1[Init Value]), all(Date) ) + calculate(sum(Table2[Value]), filter( all(Date) , date[Date] <= Max(Date[Date]) ) )

or something like

CALCULATE(firstnonblankvalue('Date'[Month],sum(Table[Intial Inventory])),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=maxx(date,date[date])))

You can also consider window function

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[Pmorg-73]

Thanks fro helping

I am very confused by your code there. Can we write this to delineate between the credit card transaction "Date" and the overall calendar "date"

The first line of calculate gave me a syntax error also