Forum Discussion
Helper Iadding running total to a single balance value table
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
Hi,
See if my solution in the attached files help.
3 Replies
- Ashish_Mathur
Super User
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-73Thanks 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
