Cumulative Total - Zero Period, Balance Sheet Accounts

[dstramilov]

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.

[v-yuezhe-msft]

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

[dstramilov]

Hello,

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.

[v-yuezhe-msft]

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

[dstramilov]

Lydia,

Think about this as you got checking bank account. 

You got deposits & withdrawls and at any point of time you can see your running total balance.

Problem with bank, that you can't pull all historical information 10 year history, but can pull 2-3 years, so any fiscal period start balance is period ZERO in my example. Column you added Column 2 should equal to Balance column, curreny it is not.

You need to use debit & credit column to find running total.

Thank you fro your help. 

[v-yuezhe-msft]

Hi @dstramilov,

Please help to post the expected result here so that we can provide you appropriate method.

Thanks,
Lydia Zhang

[dstramilov]

@v-yuezhe-msft take a look at attached file, please.

https://dl.dropboxusercontent.com/u/28420733/BS_Period_Zero.xlsx

Period zero allows us to query just 2016 fiscal year and get beginning balance for Balance Sheet accounts.

[dstramilov]

@v-yuezhe-msft see my update, please.

[dstramilov]

@v-yuezhe-msft  do you have any ideas how to do this?

[dstramilov]

Do you need extra info details on this topic?

[Rui]

Hi,

I'm struggeling with the same problem. Did you manage to solved this? If so please share how you did it.

Thanks,

Rui

[dstramilov]

No, this is still an open item for me. What accounting system do you use?