Unable to get Opening Stock and Closing Stock (Quantity and Amount)

[nagaraj007]

Hello All,

I am trying to get the data in the attached image format. I have used 3 dax functions to get the output. However its not showing the right numbers. Please advice.

Movement :  

Movement = calculate(sum(Aging[Qty.]), Aging[Type]="Purchased")

-calculate(sum(Aging[Qty.]), Aging[Type]="Issued")+0

Opening Stock:

Opening Stock =

CALCULATE (

[Movement] ,

FILTER (

ALLEXCEPT ( Aging, Aging[Item Details] ),

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

)

)

Closing Stock =

CALCULATE (

[Movement],

FILTER (

ALLEXCEPT( Aging, Aging[Item Details] ),

Aging[Date] <= MAX ( 'Calendar'[Date])

)

)

[nagaraj007 · Accepted solution]

Thank you Dedmon for the help. I got the solution what i was looking out for. But one small query, i need to show the opening amount value also against opening stock quantity, how to show that?

[v-deddai1-msft]

Hi @nagaraj007 ,

Please refe to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERBXohzltIlMtJwjZr...

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

[v-deddai1-msft]

Hi @nagaraj007 ,

Try to change the measure Opening Stock：

Opening Stock = 
CALCULATE (
[Movement] ,
FILTER (ALL(Sheet1[Date]),
Sheet1[Date]< MIN ( 'Calendar'[Date] )
),CROSSFILTER(Sheet1[Date],'Calendar'[Date],None)
)

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

[v-deddai1-msft]

Hi @nagaraj007 ,

Use the following measure:

Opening Stock = 
CALCULATE (
[Movement] ,
FILTER (
ALLEXCEPT ( Aging, Aging[Group] ),
Aging[Date] < MAX ( 'Calendar'[Date] )
),CROSSFILTER(Aging[Date],'Calendar'[Date],None)
)

Closing Stock = 
CALCULATE (
[Movement],
FILTER (
ALLEXCEPT( Aging, Aging[Group] ),
Aging[Date] <= MAX ( 'Calendar'[Date])
),CROSSFILTER(Aging[Date],'Calendar'[Date],None)
)

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERBXohzltIlMtJwjZr...

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

[nagaraj007]

Hi @v-deddai1-msft ,

I have sent personal message with the updated PBIX file which has current data. Data is not showing accurately. Please advise.

[v-deddai1-msft]

Hi @nagaraj007 ,

Would you please show us your sample data again? I didn't save the pbix file yesterday.

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

[nagaraj007]

Hi @v-deddai1-msft ,

Please find the Sample Data 

[v-deddai1-msft]

Hi @nagaraj007 ,

Please try the following measure:

Opening Stock = 
CALCULATE (
[Movement] ,
FILTER (
ALLEXCEPT ( Aging, Aging[Item Details] ),
Aging[Date] < MAX ( 'Calendar'[Date] )
),CROSSFILTER(Aging[Date],'Calendar'[Date],None)
)

Closing Stock = 
CALCULATE (
[Movement],
FILTER (
ALLEXCEPT( Aging, Aging[Item Details] ),
Aging[Date] <= MAX ( 'Calendar'[Date])
),CROSSFILTER(Aging[Date],'Calendar'[Date],None)
)

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

[nagaraj007]

Hi @v-deddai1-msft ,

Data is not showing accurately. If you could share me th PBIX file based on sample data it would be of great help.

[nagaraj007]

Hi @v-deddai1-msft ,

If you could share me the PBIX file based on sample data that would be of great help, as this file is on HOLD only because of this. 

Thanking you in advance

[v-deddai1-msft]

Hi @nagaraj007 ,

Would you please show us your sample data for it. It is quitely difficult for me to help you with the issue without support of data.

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

[nagaraj007]

Hi @v-deddai1-msft ,

Please find the Sample Data.  

[v-deddai1-msft]

Hi @nagaraj007 ,

Did you have data for gift items in May? In the second  solution, you will also need to set show item with no data option for [Item Details]

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

[nagaraj007]

Hi @v-deddai1-msft ,

There is no data for the month of May for Gift items, however in the month of April closing balance Gift items had a value. Now in the month of May under Opening Balance it should reflect that value which was the closing balance of April . Please advise.

[v-deddai1-msft]

Hi @nagaraj007 ，

Are you talking about show items with no data? There is no data for Gift Items in May，but you want to show item for it in May. 

First try to use show item for no data for [Item Details] by right click it. Or you can try to create a new table with column of all [Item Details]. Add it to rows. Then use the following measure:

Opening Stock =
CALCULATE (
[Movement] ,
FILTER (
ALL( Aging),Aging[Item Details] IN DISTINCT('Table'[Item Details])&&
Aging[Date] < MAX ( 'Calendar'[Date] )
)
)
 
Closing Stock =
CALCULATE (
[Movement],
FILTER (
ALL( Aging),Aging[Item Details] IN DISTINCT('Table'[Item Details])&&
Aging[Date] <= MAX ( 'Calendar'[Date] )
)
)

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

[nagaraj007]

Hi @v-deddai1-msft ,

I tried to do in both ways which you told me, its still not showing gift items.

Please advise.

[v-deddai1-msft]

Hi @nagaraj007 ,

I didn't understand your logic, so I tried to analyze the results in the screenshot you gave me. Please refer to the following two measure:

Opening Stock = 
CALCULATE (
[Movement] ,
FILTER (
ALLEXCEPT ( Aging, Aging[Item Details] ),
Aging[Date] < MIN ( 'Calendar'[Date] )
)
)

Closing Stock = 
CALCULATE (
[Movement],
FILTER (
ALLEXCEPT( Aging, Aging[Item Details] ),
Aging[Date] <= MAX ( 'Calendar'[Date])
)
)

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

[nagaraj007]

Hi , 

I have used the measure to get the opening and closing stock. But however when I add Item Details as drilldown in the matrix it doesnt show all data. Please advice. (Example - Gift Items is shown in April but its not showing in May month as it doesnt have any purchase or Issued. 

[nagaraj007]

Problem:
The line for the Items is not shown for which there is no activity during the selected period (date, month or year). Activity means Purchased and Issued.

@v-deddai1-msft 

[v-deddai1-msft]

Hi @nagaraj007 ，

How did you get 10,20,1000 from input table ?

Best Regards,

Dedmon Dai