Inventory Projection

[eechever]

Hi, everyone.

I've been working on this problem for a week and can't find a solution. 

I have three products A, B, and C with the following information:
Product A: Current Stock Quantity 163

MonthYear	NumMonthYear	Inc_Shipment_Qty	SalesForecast_Qty
Nov-22	24274	400	115
Dec-22	24275		108
Jan-23	24276		93
Feb-23	24277	200	110
Mar-23	24278		132
Apr-23	24279		131

Product B: Current Stock Quantity 0

MonthYear	NumMonthYear	Inc_Shipment_Qty	SalesForecast_Qty
Nov-22	24274		22
Dec-22	24275	37	22
Jan-23	24276	40	22
Feb-23	24277	28	22
Mar-23	24278		22
Apr-23	24279		22

Product C: Current Stock Quantity 0

MonthYear	NumMonthYear	Inc_Shipment_Qty	SalesForecast_Qty
Nov-22	24274		60
Dec-22	24275		60
Jan-23	24276		60
Feb-23	24277	180	60
Mar-23	24278		60
Apr-23	24279		60

What I want to have is a Measure that calculates the projection of the inventory for those three products according to the following rules:
For Nov-22 it is just the Current Stock Quantity + Inc_Shipment_Qty - SalesForecast_Qty
For the other months it is the value from the previous month + Inc_Shipment_Qty - SalesForecast_Qty

If the value of the measure is less than 0, just show 0

With that measure I just want to build a matrix just like this one:

Product	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23
A	448	340	247	337	205	74
B	0	15	33	39	17	0
C	0	0	0	120	60	0

Thank you in advance for any help provided

[eechever]

Ok so this solution solve the problem for these particular tables. But how about any table? Either way, thank you very much @v-binbinyu-msft for your reply

[v-binbinyu-msft · Accepted solution]

Hi @eechever ,

Please try to create measure with below dax formula:

Measure =
VAR cur_product =
    SELECTEDVALUE ( 'Table 2'[Product ] )
VAR cur_monthyear =
    SELECTEDVALUE ( 'Table 2'[MonthYear] )
VAR trans =
    FORMAT ( cur_monthyear, "mmmm yyyy" )
VAR init_quantity_a = 163
VAR init_quantity_b = 0
VAR init_quantity_c = 0
VAR cur_Inc_Shipment_Qty_a =
    CALCULATE (
        MAX ( 'Product A'[Inc_Shipment_Qty] ),
        'Product A'[MonthYear] = cur_monthyear
    )
VAR cur_Inc_Shipment_Qty_b =
    CALCULATE (
        MAX ( 'Product B'[Inc_Shipment_Qty] ),
        'Product B'[MonthYear] = cur_monthyear
    )
VAR cur_Inc_Shipment_Qty_c =
    CALCULATE (
        MAX ( 'Product C'[Inc_Shipment_Qty] ),
        'Product C'[MonthYear] = cur_monthyear
    )
VAR cur_SalesForecast_Qty_a =
    CALCULATE (
        MAX ( 'Product A'[SalesForecast_Qty] ),
        'Product A'[MonthYear] = cur_monthyear
    )
VAR cur_SalesForecast_Qty_b =
    CALCULATE (
        MAX ( 'Product B'[SalesForecast_Qty] ),
        'Product B'[MonthYear] = cur_monthyear
    )
VAR cur_SalesForecast_Qty_c =
    CALCULATE (
        MAX ( 'Product C'[SalesForecast_Qty] ),
        'Product C'[MonthYear] = cur_monthyear
    )
VAR tmp1 =
    FILTER ( ALL ( 'Product A' ), 'Product A'[MonthYear] <= cur_monthyear )
VAR tmp2 =
    FILTER (
        ALL ( 'Product B' ),
        'Product B'[MonthYear] <= cur_monthyear
            && 'Product B'[Inc_Shipment_Qty] <> BLANK ()
    )
VAR tmp3 =
    FILTER (
        ALL ( 'Product C' ),
        'Product C'[MonthYear] <= cur_monthyear
            && 'Product C'[Inc_Shipment_Qty] <> BLANK ()
    )
VAR tmp4 =
    FILTER (
        ALL ( 'Product C' ),
        'Product C'[MonthYear] >= DATE ( 2023, 2, 01 )
            && 'Product C'[MonthYear] <= cur_monthyear
    )
VAR tmp5 =
    FILTER ( ALL ( 'Product B' ), 'Product B'[MonthYear] <= DATE ( 2023, 2, 01 ) )
VAR _value_for_b =
    init_quantity_c + SUMX ( tmp5, [Inc_Shipment_Qty] )
        - SUMX ( tmp5, [SalesForecast_Qty] )
VAR sum_Inc_Shipment_Qty_a =
    SUMX ( tmp1, [Inc_Shipment_Qty] )
VAR sum_SalesForecast_Qty_a =
    SUMX ( tmp1, [SalesForecast_Qty] )
VAR sum_Inc_Shipment_Qty_b =
    SUMX ( tmp2, [Inc_Shipment_Qty] )
VAR sum_SalesForecast_Qty_b =
    SUMX ( tmp2, [SalesForecast_Qty] )
VAR sum_Inc_Shipment_Qty_c =
    SUMX ( tmp3, [Inc_Shipment_Qty] )
VAR sum_SalesForecast_Qty_c =
    SUMX ( tmp3, [SalesForecast_Qty] )
VAR _value =
    SWITCH (
        TRUE (),
        cur_product = "A"
            && trans = "January 2023",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "A"
            && trans = "February 2023",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "A"
            && trans = "March 2023",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "A"
            && trans = "April 2023",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "A"
            && trans = "November 2022",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "A"
            && trans = "December 2022",
            init_quantity_a + sum_Inc_Shipment_Qty_a - sum_SalesForecast_Qty_a,
        cur_product = "B"
            && trans = "January 2023",
            init_quantity_b + sum_Inc_Shipment_Qty_b - sum_SalesForecast_Qty_b,
        cur_product = "B"
            && trans = "February 2023",
            init_quantity_b + sum_Inc_Shipment_Qty_b - sum_SalesForecast_Qty_b,
        cur_product = "B"
            && trans = "March 2023", _value_for_b,
        cur_product = "B"
            && trans = "April 2023", _value_for_b - cur_SalesForecast_Qty_b,
        cur_product = "B"
            && trans = "November 2022",
            init_quantity_b + sum_Inc_Shipment_Qty_b - sum_SalesForecast_Qty_b,
        cur_product = "B"
            && trans = "December 2022",
            init_quantity_b + sum_Inc_Shipment_Qty_b - sum_SalesForecast_Qty_b,
        cur_product = "C"
            && trans = "January 2023",
            init_quantity_c + sum_Inc_Shipment_Qty_c - sum_SalesForecast_Qty_c,
        cur_product = "C"
            && trans = "February 2023",
            init_quantity_c + SUMX ( tmp4, [Inc_Shipment_Qty] )
                - SUMX ( tmp4, [SalesForecast_Qty] ),
        cur_product = "C"
            && trans = "March 2023",
            init_quantity_c + SUMX ( tmp4, [Inc_Shipment_Qty] )
                - SUMX ( tmp4, [SalesForecast_Qty] ),
        cur_product = "C"
            && trans = "April 2023",
            init_quantity_c + SUMX ( tmp4, [Inc_Shipment_Qty] )
                - SUMX ( tmp4, [SalesForecast_Qty] ),
        cur_product = "C"
            && trans = "November 2022",
            init_quantity_c + sum_Inc_Shipment_Qty_c - sum_SalesForecast_Qty_c,
        cur_product = "C"
            && trans = "December 2022",
            init_quantity_c + sum_Inc_Shipment_Qty_c - sum_SalesForecast_Qty_c
    )
RETURN
    IF ( _value > 0, _value, 0 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.