Calculate Shortage Date
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!
