# Calculation of Opening and Closing Balance (DAX)



## tusharmehta (May 20, 2015)

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. 


Location Wise StockMonthOpeningABSGDCTCPADGClosing BlanceJan10,0005001,00030020012,000Feb300900400200Mar4001,000500150Apr5001,287455450May600999343300Jun4501,500786200JulAugSepOctNovDec10,0002,7506,6862,7841,50012,000

<tbody>

</tbody>
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 (May 20, 2015)

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


Excel 2012ABCDEFG1Location Wise Stock2MonthOpeningABSGDCTCPADGClosing Blance3Jan100005001000300200120004Feb12000300900400200138005Mar138004001000500150158506Apr158505001287455450185427May18542600999343300207848Jun207844501500786200237209Jul237202372010Aug237202372011Sep237202372012Oct237202372013Nov237202372014Dec23720237201510000275066862784150023720Sheet15Cell FormulasRangeFormulaG3=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=G3B5=G4B6=G5B7=G6B8=G7B9=G8B10=G9B11=G10B12=G11B13=G12B14=G13B15=B3C15=SUM(C3:C14)D15=SUM(D3:D14)E15=SUM(E3:E14)F15=SUM(F3:F14)


----------



## tusharmehta (May 21, 2015)

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 (May 21, 2015)

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


----------



## tusharmehta (May 23, 2015)

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 (May 24, 2015)

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


----------



## TimRodman (May 25, 2015)

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 (May 26, 2015)

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 (May 26, 2015)

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 (May 27, 2015)

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.


----------



## tusharmehta (May 20, 2015)

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. 


Location Wise StockMonthOpeningABSGDCTCPADGClosing BlanceJan10,0005001,00030020012,000Feb300900400200Mar4001,000500150Apr5001,287455450May600999343300Jun4501,500786200JulAugSepOctNovDec10,0002,7506,6862,7841,50012,000

<tbody>

</tbody>
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.


----------



## TimRodman (May 27, 2015)

The phrase "I would probably start here" at the end of my previous post is a link.


----------



## tusharmehta (May 30, 2015)

Thanks I found the url and I will start looking into it.

Appreciate for your responses and guidance.


----------



## Ramzan11b (Apr 30, 2019)

tusharmehta said:


> Thanks I found the url and I will start looking into it.
> 
> Appreciate for your responses and guidance.


Hy tusharmehta! Have you got the solution?
Please share the solution with me. I also need to calculate opening closing balance for power pivot but i am facing problems.


----------

