Calculate balance of account

[Greger1337]

Hi,

I have data in two tables plus a separate date table.

1st table have all the opening balances of accounts 2013-07-01.

2nd table have all transactions of the accounts.

I want to calculate the montly balance of the accounts.

First I created:

Balance = SUM('BFO BR Transactions'[Amount]) + SUM('BFO Balance'[OpeningBalance])

And to calculate cummulative balance:

Cummulated balance = 
CALCULATE([Balance]; ALL('BFO BR Transactions'[Date]);
          DATESINPERIOD('Date'[Date]; 
                        LASTDATE('BFO BR Transactions'[Date]);-1000;MONTH
                       )
         )

This works as intended until I add a date slicer and I slice beyond a date where a account don't have any transactions.

For examle, one account have had the same balance since 2014-02-02 and therefore no transactions after this date. If I slice to see the balances for 2016 the end balance 2014-02-02 for the mentioned account is not in the sum.

What have I done wrong here?

[v-yulgu-msft]

Hi @Greger1337,

Would you please share your sample data to elaborate your scenario?

Best regards,
Yuliana Gu

[Greger1337 · Accepted solution]

Thank you for taking your time to help 🙂

I managed to solve this myself by change a filter from page filter to include it in the DAX formula.

Cummulated balance = CALCULATE(
                              CALCULATE(SUM('BFO BR Transactions'[Amount]); 
                              FILTER('BFO BR Transactions';
                                   'BFO BR Transactions'[Account] >= 1910 
                                    && 'BFO BR Transactions'[Account] <= 1941)) 
                            + CALCULATE(SUM('BFO Saldo'[Opening balance]);
                              FILTER('BFO Saldo';
                                    'BFO Balance'[Account] >= 1910 
                                    && 'BFO Balance'[Account] <= 1941))   
                            ;ALL('BFO BR Transactions'[Date]);
                                DATESINPERIOD('Date'[Date]; 
                                LASTDATE('BFO BR Transactions'[Date]);-1000;MONTH
                            )
                  )

With this I get the correct balance at each month regardless of how I adjust my date slicer.