Cumulative Banking Transactions - ALL vs ALLSELECTED within cumulative totals


#1

Hi All,

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)

Hmmmm. Any help would be great.

Thanks
Cam


#2

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?


#3

Hi Gary,

Thanks for getting in touch and responding.

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.

Cheers

Cam


#4

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…

Opening Ledger = CALCULATE([Closing Ledger], DATEADD( ‘Calendar’[Date], -1,YEAR) )


#5

Which context do you need the results for.

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

Also check here, I made a quick video of how to post formulas into the forum - How Place DAX Formula Into Forum Topics & Posts


#6

Hi Sam,

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.

Cheers

Cam


#7

Right, how about something like this.

Total Bank Transactions =
[Opening Bank Balance] +
SUMX( SUMMARIZE( Dates, “Cumulative Balances”, [Bank Transactions (Cumulative ) ] ), [Cumulative Balances] )

See if this works.

The ALLSELECTED formula is quite likely causing the initial problems here.


#8

Sorry am I missing a step?


#9

Sorry yes, my bad. After Dates place comma Dates[Dates]


#10


#11

Looks fine to me, I would have to see the model


#12

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


#13

Ok, thank you Sam. I’ll take another look tonight.


#14

On reflection I’m not sure if this is right. Just weekend here, I’ll do a bigger review tomorrow morning. Chrs


#15

Ok just reviewing this one,

Still trying to get my head fully around this.

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.

Let me know.


#16

Maybe you might need to break out another formula, as I just read that you need to ALLSELECTED version as well


#17

Hi Sam,

Worked it out. Measure below gets the interim Bank Transactions pre context you have put it in.

Op Bal Interim Transactions = 
VAR MinDate = CALCULATE ( 
    MIN ( Dates[Date] ), 
    ALLSELECTED ( Dates ) 
)
RETURN
CALCULATE (
    [Total Bank Transactions],
    FILTER
    ( ALL ( Dates ),
        Dates[Date] < MinDate
    )
)

Then you can bring it into your normal Cumulative Calc

Bank Transactions (Cummulative) = [Opening Bank Balance] + [Op Bal Interim Transactions] +
CALCULATE( 
	[Total Bank Transactions] , 
	FILTER(
		ALLSELECTED( Dates),
		Dates[Date] <= MAX( Dates[Date])
	)
)

#18

Super that’s great. Glad we got there.


#19

Thanks Sam.

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.


#20

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

    )
    )