topic Re: Calculate Shortage Date in DAX Commands and Tips

Calculate Shortage Date

powerbidverdon — Tue, 29 Nov 2022 19:55:54 GMT

Hello community, thank you for taking the time to help my learning 🙂

Problem:

Need to calculate the date when my stock level is zero or less.

I have the following 3 tables all connected to an Index Table by the column Key. Column Key is just all possible combinations of Item Number and Date, for example "AAA-29/11/2022"

I would like to calculate the date when my item stock level is zero or less.

For example:

I have item AAA. From the Quantity On Hand table I see I have 5 pcs in stock on 29th Nov (Date is just todays date ). In the Requirements table I see i require 4 pcs on 30 Nov , 3 pc on 1 Dec and 5 pcs on 2 Dec. In the Purchase Orders table I have a Purchase order coming in on 30 Nov of 3 pcs and another on 1 Dec of 1 pc.

Explanation in table form below

Date	Item	On Hand	Req	On Order	Adj Stock
29 Nov (today)	AAA	5			5
30 Nov	AAA		4	3	4 (5-4+3)
1 Dec	AAA		3	1	2 (4-3+1)
2 Dec	AAA		5		-3 (2-5)

For AAA I would like the calculation to give me as result the date 2 Dec as it is the earliest date when stock level is >= 0 pcs.

Using this measure I would like the table visualisation output to look like this

Item Number	Iventory run out date
AAA	2 Dec 2022
BBB	15 Jan 2023
CCC	4 Jan 2023
DDD	15 Feb 2023
.....	....

I can't see to get my head round how to do this. I think I need to create loop to find when running adjusted stock reaches zero or less but is this possible in Dax?

Thanks!

Re: Calculate Shortage Date

amitchandak — Wed, 30 Nov 2022 02:36:02 GMT

powerbidverdon , refer these two approches

https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model
https://blog.enterprisedna.co/calculate-days-of-zero-stock-power-bi-inventory-management-insights/

Re: Calculate Shortage Date

powerbidverdon — Wed, 30 Nov 2022 09:51:07 GMT

Hello Amit,

Thanks for the feedback.

Unfortunately both suggestions would not work in my case.

-radaclad solution does not give me back the Date the part will be short. It simply gives a running total which I am already doing today. The solution is also just efficient for a couple of product IDs produced in a matrix. I am looking for a simple table as below for potentially 1000s of items.

Required ouput

Item Number	Iventory run out date
AAA	2 Dec 2022
BBB	15 Jan 2023
CCC	4 Jan 2023
DDD	15 Feb 2023
..... (1000 more item numbers)	....

-the second solution also seems to be modeled on product inventory whereas mine is based on purchasing parts for production (demand requirement). It is also using running total and it is looking at a part by part level, it is not feasible for a solution where i would like a table listing around 1000 parts and in another column the day the part will be out of stock (see solution table above).

thanks

Re: Calculate Shortage Date

Anonymous — Tue, 06 Dec 2022 05:53:38 GMT

Hi powerbidverdon ,

Have trouble to follow.

Can you explain how you got the desired result from the data you have?

Re: Calculate Shortage Date

franco28 — Fri, 16 Jun 2023 18:43:34 GMT

Hi powerbidverdon,

I was wondering whether you could find a solution for your problem. I am trying to achieve something very similar.

Thank you in advanced.