Forum Discussion
Help: Building a deposit/withdrawal Calculation
I've been racking my brain around this and thought that I would ask the general community.
Use Case
There are manual deposits and withdrawals into tanks and what we're attempting to build is something that show a running total of how much should be in the tank when we measure it.
Example
Explaination
The "Starting Level" is yesterday's "End Level". Deposits and and withdrawals are then recalculated for that day by using the math of (("Starting Level" + "Deposits") - "Withdrawal") = "End Level"
When I attempt to do this I get a circular logic error, which makes sense, but maybe I'm just looking at this through the wrong lens.
Is there any way to do this?
Based on your description, it seems that you need to get the runngint total to previous day for starting level, and runngint total to current day for end level. You can use the DAX below to create your measure.
Starting Level = CALCULATE(SUM(Table1[Deposit])-SUM(Table1[Withdrawal])+100,FILTER(ALL(Table1[Date]),Table1[Date]<MAX(Table1[Date])))
End Level = CALCULATE(SUM(Table1[Deposit])-SUM(Table1[Withdrawal])+100,FILTER(ALL(Table1[Date]),Table1[Date]<=MAX(Table1[Date])))Reference
http://www.daxpatterns.com/cumulative-total/Regards,
Charlie Liao
2 Replies
You should consider simply NOT adding calculated columns for Starting/Ending levels.
If you only had Date, Deposits, Withdrawls in the table, and a separate storage for initial starting level... the rest can be calculated in measures.
Current Balance := [Initial Balance] + CALCULATE(SUM(Transactions[Deposit]) - SUM(Transactions[Withdrawal]), LASTDATE(Calendar))
Based on your description, it seems that you need to get the runngint total to previous day for starting level, and runngint total to current day for end level. You can use the DAX below to create your measure.
Starting Level = CALCULATE(SUM(Table1[Deposit])-SUM(Table1[Withdrawal])+100,FILTER(ALL(Table1[Date]),Table1[Date]<MAX(Table1[Date])))
End Level = CALCULATE(SUM(Table1[Deposit])-SUM(Table1[Withdrawal])+100,FILTER(ALL(Table1[Date]),Table1[Date]<=MAX(Table1[Date])))Reference
http://www.daxpatterns.com/cumulative-total/Regards,
Charlie Liao
