Calculate The Date Where There Is Enough Supply To Satisfy Dem

[alindquist]

I am trying to come up with a solution to the following problem.

Problem:

In my dataset I have shop orders that require a certain quantity of material (Demand), and purchase orders that re-supply that material (Supply). I need to determine for each shop order, what is the first date where I will have enough supply to fill the demand.

Sample dataset:

RowNum	Part Number	Transaction Date	Supply Quantity	Demand Quantity	Source	Order Number	Running Inventory Total
1	ABC	9/29/2021		4	Shop Order	SO123	-4
2	ABC	9/30/2021		2	Shop Order	SO345	-6
3	ABC	10/1/2021		8	Shop Order	SO567	-14
4	ABC	10/2/2021	2		Purchase Order	PO111	-12
5	ABC	10/3/2021	2		Purchase Order	PO222	-10
6	ABC	10/4/2021		6	Shop Order	SO890	-16
7	ABC	10/5/2021	6		Purchase Order	PO333	-10
8	ABC	10/6/2021	4		Purchase Order	PO444	-6
9	ABC	10/7/2021	6		Purchase Order	PO555	0

For example, on 9/29/2021 (row 1) I have shop order SO123 that will require 4 units of material, bringing my total available inventory to -4. I then have 2 more shop orders further depleting the inventory to -14 (-4 - 2 - 8 = -14). Then on 10/2/2021 (row 4) I receive a purchase order with a supply of 2 units to bring the inventory up to -12. The next day I receive another 2 units bringing the total up to -10. I can now use those combined 4 units of supply to satisfy the demand for shop order SO123. So 10/3/2021, the date that brought my supply up by 4 total units, would be the first date I would have enough material supplied to satisfy the demand of that shop order. Then on 10/5/2021 I receive an additional 6 units of material, which is enough to satisfy the demand of shop order SO345, so 10/5/2021 is the date I would return for shop order SO345. I would need to continue this logic for each of the shop orders to determine the earliest date where I would have enough material for that order.

The desired output would be:

Order Number	Supply Date
SO123	10/3/2021
SO345	10/5/2021
SO567	10/6/2021
SO890	10/7/2021

I'm not sure if something like this is possibly in Power Query. It seems like I need to get a running total of just the supply quantity and find the date where (running inventory total + running supply quantity) >= 0.

For example, the running supply quantity would be 2 on 10/2/2021, 4 on 10/3/2021, 10 on 10/5/2021, 14 on 10/6/2021 and 20 on 10/7/2021. So I could tell that for SO123 the point where running inventory total is balanced out by running supply quantity is on 10/3/2021 (-4 + 4 >= 0 is true).

Hopefully that all makese sense.

[v-kelly-msft · Accepted solution]

Hi  @alindquist ,

Using dax would be much easier.

Create a measure as below:

Measure2 =
VAR _demand =
    0
        - SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Part Number] = MAX ( 'Table'[Part Number] )
                    && 'Table'[RowNum] <= MAX ( 'Table'[RowNum] )
            ),
            'Table'[Demand Quantity]
        )
VAR _supply =
    CALCULATE (
        SUM ( 'Table'[Supply Quantity] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[RowNum] <= MAX ( 'Table'[RowNum] )
                && 'Table'[Part Number] = MAX ( 'Table'[Part Number] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Source] ) = "Shop Order",
        CALCULATE (
            MIN ( 'Table'[Transaction Date] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Part Number] = MAX ( 'Table'[Part Number] )
                    && 'Table'[Supply running total] >= ABS ( MAX ( 'Table'[Demand running total] ) )
                    && 'Table'[Supply running total] <> BLANK ()
            )
        )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[lbendlin]

Are orders always fulfilled First Come First Serve?  What happens when two or more orders come in on the same day?