Closing and Opening of stocks.

[Ibadkhan]

Hey!

I am working to calculate the opening and closing stocks on daily basis. I have set the fixed value of opening for the period of july and rest is calculating correctly. Issue is that when i select september in date filter, the result is incorrect because i have fixed the opening for the period of july, and when i select july, august and september in date filter, it show the closing of september correctly.

Other issue is that i have to make closing of previous month as opening of current month, and i when i select september in date filter, i want the closing of august as opening of september.

Inshort when i select september in filter, i want closing of july+august+september as closing of september and closing of july+ august as opening of september, and i have to do this all by selecting only one month in filter. Need to resolve this issue. 

Best Regards,

Ibad.

[Greg_Deckler]

@Ibadkhan - Hard to visualize. Can you share sample data? Maybe Lookup Min/Max? https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@Ibadkhan , refer if these can help

openingbalancemonth,openingbalancequarter, openingbalanceyear
closingbalancemonth ,closingbalancequarter, closingbalanceyear

I created a video on how to use

https://youtu.be/yPQ9UV37LOU

https://youtu.be/6lzYOXI5wfo

[Ibadkhan]

@Greg_Deckler  @amitchandak 

I want this issue to be solved.

If i select september in filter, i want closing of july+august+september as closing of september and closing of july+ august as opening of september, and i have to do this all by selecting only one month in filter.

Is it possible?

[amitchandak]

@Ibadkhan , yes you can , like

closingbalancemonth(Sum(Table[Value]), Date[Date])+
closingbalancemonth(Sum(Table[Value]), dateadd(Date[Date],-1,month))+closingbalancemonth(Sum(Table[Value]), dateadd(Date[Date],-2,month))

[Ibadkhan]

@amitchandak @Greg_Deckler i just want two thing, i'm having two card visuals, one for closing and one for opening, and one date slicer.

The scenario that i want to apply is when i select september month in date slicer, in opening card visual that should be filtered for sum of closing of all previous month .i.e. july and august in my case excluding august, and in closing slicer i want sum of closing of all years .i.e. closing(july) + closing(august) + closing(september).

This is what want to do. And i dont want to fix it for september, it should wor for coming month in future also like,

opening(november) = closing(july)+closing(august)+closing(september)

closing(november) = closing(july)+closing(august)+closing(september)+closing(november)

and so on for the coming months in future.

Note: I have calculated the closing by a measure.

[v-deddai1-msft · Accepted solution]

Hi @Ibadkhan ,

Your issue is like YTD for fiscal year. So you can use the following measure for opening and closing:

closing = TOTALYTD ( SUM ( Table[Column] ), 'Calendar'[Date], "06/30" )
opening = CALCULATE([closing],DATEADD ( 'Calendar'[Date], - 1, Month ))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Ibadkhan]

@amitchandak you help is appreciated, but i want it on permenant basis fo future also, you formula can work only for the moenth of september, I need same thin for october, november and so on.

[amitchandak]

@Ibadkhan , where did you sep-2020 fixed in the formula ? I think it should work. Have you tried out?

[Ibadkhan]

@amitchandak  i have only ixed the opening of july as a staring point of fiscal yeas so its calculating the closing of each month properly, closing of july only contains the closing of july, closing of august in correct when we take the closing of july and august together .i.e. closing august = closing july + closing of august, same for september .i.e. closing of september is correct by closing july + closing august + closing september. I have only fixed the opening of july as a starting because of new fiscal year.