Forum Discussion
Solved
Inventory Projection
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
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.
2 Replies
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.Ok so this solution solve the problem for these particular tables. But how about any table? Either way, thank you very much Anonymous for your reply
