Unable to get Opening and closing stock amount

[nagaraj007]

Hi ,

I am unable to get the opening and closing stock amount. please advise.

Attached is the PBIX 

@v-deddai1-msft 

[v-stephen-msft]

Hi @nagaraj007 ,

Sorry for my late reply.

The MAX('Calendar'[Date]) in your Opening Stock measure returns the 12/31/2020. It's the max date in your calendar table.

Could you please tell me what you want to filter with Sheet1[Date]< MAX ('Calendar'[Date] )?

CROSSFILTER specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. Do you want to cancel the relationship between the two tables?

Best Regards,

Stephen Tao

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

[nagaraj007]

Hi @v-stephen-msft ,

Measure is working perfectly and showing correct results for opening stock QUANTITY. 

So i am looking for opening stock VALUE measure. Could you please help me 

[nagaraj007]

Hi @v-stephen-msft ,

Could you please help me

[nagaraj007]

Could any one please help me?

[v-stephen-msft]

Hi @nagaraj007 ,

What's the calculation logic of the Opening Stock Value?

Sorry, I didn't find the calculation logic of Opening Stock Value in your file.

Best Regards,

Stephen Tao

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

[nagaraj007]

Hi @v-stephen-msft ,

The logic for opening stock is 

Opening Stock =

CALCULATE (

[Movement] ,

FILTER (

ALLEXCEPT ( Sheet1, Sheet1[Group],Sheet1[Item Details] ),

Sheet1[Date]< MAX ( 'Calendar'[Date] )

),CROSSFILTER(Sheet1[Date],'Calendar'[Date],None)

)

This is also available in the PBIX file which has a measure named opening stock

[v-stephen-msft]

Hi @nagaraj007 ,

The Opening Stock measure was found. I want to ask about the logic of Opening Stock value which you framed it with a red box.

Best Regards,

Stephen Tao

[nagaraj007]

Hi @v-stephen-msft ,

Could you please help me to solve this issue

[nagaraj007]

I have used the below logic, however the total amount as of now is not showing correctly. 

I do have Price per unit column also in the PBIX file, i tried to get the qty*price but its showing wrong numbers, so i decided to use amount directly which is near to the output. But the management wants to see the exact number

Opening Stock value = CALCULATE (
[Movement op value] ,
FILTER (all(Aging[Date] ),
Aging[Date] < min( 'Calendar'[Date] )
),CROSSFILTER(Aging[Date],'Calendar'[Date],None)
)

Movement op value = calculate(sum(Aging[Amount]), Aging[Type]="Purchased")-calculate(sum(Aging[Amount]), Aging[Type]="Issued")+0

[nagaraj007]

Hi @v-stephen-msft ,

Could you please help me

[amitchandak]

@nagaraj007 , Not sure I got it. But I think that is what it is showing. If needed you can add +0 to opening and closing stock

Can you share output in table format?

[nagaraj007]

Please find the output. I want to show Opening stock qty and also opening stock amount. 

[nagaraj007]

hi @amitchandak ,

I have attached the output required. Could you please help me

[nagaraj007]

Hi,

could anyone help me?

[amitchandak]

@nagaraj007 , Please find the file attached after signature can help

[nagaraj007]

Hi @amitchandak ,

I am looking out for the total amount against opening stock as well as closing stock.