Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
Hi @Greger1337,
Would you please share your sample data to elaborate your scenario?
Best regards,
Yuliana Gu
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |