Calculation of Opening and Closing Balance (DAX)

[tusharmehta]

Hello Everyone,

How are you doing ?

I have came across to some type of situation where I need to transfer calculated closing balance figures next month opening balance figure.

I have tried using calculation and other dax formula but I am not able to produce the result which I am looking for.

It seems I am missing something and it could be simple but I am not able to to figure out.

So will you please guide me the same.

[TABLE="class: grid, width: 489, align: center"]
<tbody>[TR]
[TD="colspan: 7, align: center"]Location Wise Stock[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Opening[/TD]
[TD]ABS[/TD]
[TD]GDC[/TD]
[TD]TCP[/TD]
[TD]ADG[/TD]
[TD]Closing Blance[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12,000[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1,287[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD][/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]1,500[/TD]
[TD="align: right"]786[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]2,750[/TD]
[TD="align: right"]6,686[/TD]
[TD="align: right"]2,784[/TD]
[TD="align: right"]1,500[/TD]
[TD="align: right"]12,000[/TD]
[/TR]
</tbody>[/TABLE]

As mentioned on above mentioned sheet I need to learn how to transfer each month closing to next month and in next month it will get calculated as opening.

Thanks in Advance.

 

[DRSteele]

I think this might do it, if I understand your query properly.

Excel 2012
	A	B	C	D	E	F	G
1	Location Wise Stock
2	Month	Opening	ABS	GDC	TCP	ADG	Closing Blance
3	Jan	10000	500	1000	300	200	12000
4	Feb	12000	300	900	400	200	13800
5	Mar	13800	400	1000	500	150	15850
6	Apr	15850	500	1287	455	450	18542
7	May	18542	600	999	343	300	20784
8	Jun	20784	450	1500	786	200	23720
9	Jul	23720					23720
10	Aug	23720					23720
11	Sep	23720					23720
12	Oct	23720					23720
13	Nov	23720					23720
14	Dec	23720					23720
15		10000	2750	6686	2784	1500	23720
Sheet15

Cell Formulas
Range	Formula
G3	=B3+SUM(C3:F3)
G4	=B4+SUM(C4:F4)
G5	=B5+SUM(C5:F5)
G6	=B6+SUM(C6:F6)
G7	=B7+SUM(C7:F7)
G8	=B8+SUM(C8:F8)
G9	=B9+SUM(C9:F9)
G10	=B10+SUM(C10:F10)
G11	=B11+SUM(C11:F11)
G12	=B12+SUM(C12:F12)
G13	=B13+SUM(C13:F13)
G14	=B14+SUM(C14:F14)
G15	=B15+SUM(C15:F15)
B4	=G3
B5	=G4
B6	=G5
B7	=G6
B8	=G7
B9	=G8
B10	=G9
B11	=G10
B12	=G11
B13	=G12
B14	=G13
B15	=B3
C15	=SUM(C3:C14)
D15	=SUM(D3:D14)
E15	=SUM(E3:E14)
F15	=SUM(F3:F14)

 

[tusharmehta]

Thanks DRSteele for your response.

I guess you are responding to keep in mind excel formulas were I am inquiring with powerpivot feature, I hope my question is now more transparent.

 

[TimRodman]

Is your screenshot an actual pivot table? Are all the columns measures (calculated fields) that you created?

 

[tusharmehta]

No they are not measures, it comes from single tables and I have used power pivot table and columns.

I have thought to creating separate measures for each type, but then I have came to across point from user that how date will be display when they create new type and for that type how to create dynamic columns and add into closing total.

 

[tusharmehta]

I hope my questions is clear and no any more doubts about it ?

 

[TimRodman]

So it looks like you currently have this in Excel cells, but you want to turn it into a Pivot Table?

I haven't done much with beginning balance calculations in Power Pivot, but if I were to do something, I would probably start here.

 

[tusharmehta]

TimRodman, Thanks

Your message sounds exciting and interesting too,

It seems the topic which which are discussing looks like challenging, so let's start and see if we come across to some short of solution.

 

[TimRodman]

The basic idea of the formula in the link that I sent is that you are filtering, then summing on the data that you filtered. I just wanted to encourage you not to be intimidated by it.

 

[tusharmehta]

I haven't seen any link, do I miss anything ?

I am learning power pivot from last few months and finding very interesting and exciting and I am really thankful that Mr. Excel form gives me more boost to learn.