Forum Discussion
Helper IICumulative Total - Zero Period, Balance Sheet Accounts
Hello,
How do we calculate cumulative balance for financial data like balance sheet Asset/Liability accounts.
In our ERP GL_Summary table holds last year balance as "ZERO period".
Example 1. period Zero current fiscal year + period 1 activity = current ending balance for period 1.
Example 2. period Zero current fiscal year + period 1 activity + period 2 activity = current ending balance for period 2.
11 Replies
Hi dstramilov,
Could you please share sample data of your scenario and post expected result here?
In addition, you can review the following similar blogs about how to calculate Cumulative Total using DAX formulas.
Cumulative Total
Handling cumulative GL account balances in Power Pivot
Thanks,
Lydia Zhang
dstramilovHello,
GL Account Year Period Debit Credit Balance 1100 2015 0 0 0 1100 2015 1 50 -5 46 1100 2015 2 63 -60 51 1100 2015 3 76 -66 64 1100 2015 4 89 -72 85 1100 2015 5 102 -78 114 1100 2015 6 115 -84 151 1100 2015 7 128 -90 196 1100 2015 8 141 -96 249 1100 2015 9 154 -102 310 1100 2015 10 167 -108 379 1100 2015 11 180 -114 456 1100 2015 12 193 -120 541 1100 2016 0 206 -126 621 1100 2016 1 219 -132 709 1100 2016 2 232 -138 805 1100 2016 3 245 -144 909 1100 2016 4 258 -150 1021 1100 2016 5 271 -156 1141 1100 2016 6 284 -162 1269 1100 2016 7 297 -168 1405 1100 2016 8 310 -174 1549 1100 2016 9 323 -180 1701 1100 2016 10 336 -186 1861 1100 2016 11 349 -192 2029 1100 2016 12 362 -198 2205 Beginning balance for GL account 2016 is $621 & for 2015 $0. Period 0 allows to go back to any year & pull finincial statement (Balance Sheet) without going back all the way.
Hi dstramilov,
I am not quite sure what is your expected result. Please add a new column using the following formula and check if the following screenshot returns your desired result .
Column 2 = CALCULATE(SUM(Table2[Balance]),FILTER(ALLEXCEPT(Table2,Table2[Period]),Table2[Year]=EARLIER(Table2[Year])),FILTER(ALL(Table2),Table2[Period]<=EARLIER(Table2[Period])))
Thanks,
Lydia Zhang
