I am trying to reconcile closing bank balances and having some problems.
Easy to start. I have a table with all bank transactions, so
Total Bank Transactions = SUM(‘Bank Transactions’[Total])
Then make it cumulative;
Bank Transactions (Cummulative) =
CALCULATE(
[Total Bank Transactions] ,
FILTER(
ALLSELECTED( Dates),
Dates[Date] <= MAX( Dates[Date])
)
)
Next step, I need the opening balance of the bank account. So my Date table runs from 1/1/2015 to 31/12/2018
So I went and got my opening bank balance for 1/1/2015. Created a new Table in Power BI. Simply having the opening balance of bank account. Made a measure;
Opening Bank Balance = SUM(‘Opening Bank Balances’[Balance])
Then added it to my Cumulative Calc as follows;
Bank Transactions (Cummulative) = [Opening Bank Balance] +
CALCULATE(
[Total Bank Transactions] ,
FILTER(
ALLSELECTED( Dates),
Dates[Date] <= MAX( Dates[Date])
)
)
This obviously did not work. Which is where I am hitting problems… Because say for example I start my cumulative at 1/2/2018. The cumulative transactions are fine. But the Opening Balance is not. At 1/2/2018 the Opening Balance is calculating what the opening balance is at 1/1/2015. We do not want this amount. We want the Opening Balance to be picking up that amount plus all the interim transactions as well (1/1/2015 - 31/1/2018)
Can you illustrate how you are using the selected visual?
Doing financial statements in Power BI is a little more complex and the current matrix visual makes it difficult to output the right amounts and show the rolled up totals.
I assume the granularity of your bank transactions are daily based and perhaps your visual is trying to output the monthly or quarterly position by a fiscal year?
I’m not trying to do financials at this point in time and output a Balance Sheet in matrix format.
The bank transactions are in a table with columns being date, transaction amount, transaction type & account code. Yes, the granularity is at transactional level.
This is a cumulative DAX issue.
The Cumulative pattern that Sam has previously shared does not work in this instance using bank transactions, because we don’t want the beginning of whatever period we are referring to, to be 0. It needs to be the Opening Bank Balance plus ongoing cumulative transactions.
I suspect I need to do more with my Opening Bank Balance calculation. Possibly something along the lines of
Interim Bank Transactions = [Opening Bank Balance] +
CALCULATE( [Total Bank Transactions] ,
FILTER(
ALL( Dates),
Dates[Date] <= MIN( Dates[Date])
)
Hmmmm. Essentially as per my example. I need to get the 1/1/2015 to 31/1/2018 Bank Transactions and add them all up, to give me the Opening Bank Balance in the context/ period I am looking. Then following use the regular Cumulative pattern.
When I am showing/reconciling closing accounting balances…I work out 3 measures. Opening, Movement and Closing.
What are you using to work out your opening position and closing positions…?
In my report i am using the closing position with a dateadd -1 to work out opening position…this works at fiscal year level but NOT at a quarterly or say monthly granularity…
Can I get a few more details around the model and also a table of the current results(pics would be helpful)
I’m guessing you want to show this or filter this by customer?
I’m not sure you need to additional table. You could probably get this into one formula, and use variables.
I think you just need to find the first date a bank account was reference and then pick up that balance number?
Some combination of FILTER and ALL should do this within a variable
Just want to see a bit more around the data tables, model and context you want this in. Chrs
There is no model. I am only dealing with the one Table. ‘Bank Transactions’
I brought in a second table from Excel named Opening Balance. See screenshot. But as you mentioned, likely unnecessary. I can just enter the opening balance in DAX as variable.
See screenshot of results in table. See 1 July 2017 amount is $63k. This is wrong. That is the opening balance for 1/1/2015. Opening Balance needs to = Opening Balance at 1/1/2015 PLUS all the Cumulative Bank Transactions from 1/1/2015 - 30/06/2017.
All I’m looking to do there is form a virtual table with the cumulative total included and then summing up the total of the cumulative balance column in that SUMX
So from what I’m understanding the opening balanced number is not calculating correctly,
Because when you place a dates filter on the table the ALLSELECTED is then only calculating within that selected context.
I think actually you could maybe fix this just by changing the ALLSELECTED to ALL.
Can we try this.
I explain the difference here
The ALL will always go back to the beginning of the dataset and calculated the cumulative total from then, which is what I’m thinking you actually need now.
I’m surprised this has not come up more. If you carry transactional data that is Balance Sheet related, you will always need an Opening Balance + your Cumulative pattern. Very little information I could find on it.
For anyone who wanted to use this pattern, I had to extend the DAX from above slightly. See below gives me an accurate reconciled bank account at any point in time inside my Date table.
Op Bal Interim Transactions =
VAR MinDateInCurrentContext = CALCULATE (
MIN ( Dates[Date] ),
ALLSELECTED( Dates )
)
VAR MinDateInDateTable = CALCULATE (
MIN ( Dates[Date] ),
ALL( Dates )
)
RETURN
CALCULATE (
[Bank Transactions],
FILTER
( ALL( Dates ),
Dates[Date] > MinDateInDateTable &&
Dates[Date] < MinDateInCurrentContext
)
)
