topic Re: Opening and Closing stock in DAX Commands and Tips

Opening and Closing stock

DAIKAYA — Sat, 09 Dec 2023 13:55:58 GMT

Hello ,

I have the below Model , and I am trying to create a table that includes opening and closing stock per day.

I have the below Tables:-

- Table of Today's stock

-Table for all Purchase orders + Table for Total Inbound (Represent Total In)

-Table for all Customer orders + Table of total Outbound (Represent Total out)

My main challenge is how to move closing stock for day 2 to opening stock in day 3 and the following days.

Re: Opening and Closing stock

Fowmy — Sat, 09 Dec 2023 18:49:16 GMT

DAIKAYA

Since you have a model with many tables, it is not easy to understand how the measure should be constructed, if possible, share a PBIX file with dummy data that represents your setup and explain the expected result.

Re: Opening and Closing stock

DAIKAYA — Tue, 12 Dec 2023 17:35:30 GMT

Hello ,

Thank you for your reply.

i have prepared dummy file but i can't find the option here to upload , any advice ?

Re: Opening and Closing stock

v-zhangti — Thu, 28 Dec 2023 08:54:42 GMT

Hi, DAIKAYA

You can provide some of the example data and the desired output. Present it as a picture or EXCEL. Please go ahead and remove sensitive data.

Best Regards

Re: Opening and Closing stock

Fowmy — Thu, 28 Dec 2023 10:07:24 GMT

DAIKAYA

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

Re: Opening and Closing stock

DAIKAYA — Tue, 02 Jan 2024 11:04:26 GMT

hello ,

Thank you for your reply .

https://drive.google.com/file/d/13VY3z4ZrrrgCz8h09niMR40LP3sWBjdd/view?usp=drive_link

Please find above the Link , all data are dummy , Please note the below :-

- I have adjusted all relationships in the Model.

- My main obejective is to create Opening and Closing stock per day.

- First day the Opening stock should come from SAP_stock Table.

- First Day Closing Stock is equal to the opening stock - Outbound+Inbound

- Following day Opening stock should equal to previous day closing stock

-Following day closing stock should equal to the previous day Opening stock.

My main challenge is to create the formula , as i get a circular refrence error every time i am trying to apply the above.

Thanks alot fr the support , please let me know if you need more clarification.