Forum Discussion
SUM UP Min Measure
Hello experts,
I have this data table. I want to return MIN of Lost Sales, SOH and SIT
- If the earliest StockAvailDate is before end of current month => incluce SIT
- If the earliest StockAvailDate is after end of current month => excluce SIT
| ITEM_DESC | Lost Sales Qty (Net) | DC SOH | SIT | Earliest StockAvailDate |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 30 | 0 | 1410 | 11/19/2023 0:00 |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 15 | 0 | 1410 | 11/19/2023 0:00 |
| DW SIP MU SPF 10-PEBBLE 30ML/1FLOZ | 3 | 138 | 5 | 12/8/2023 0:00 |
| DW SIP MU SPF 10-SHELL B 30ML/1FLOZ | 5 | 0 | ||
| CRM - ULRY TL 200ML | 3 | 79 | ||
| CRM - BFUL MAG 50ML | 2 | 217 | ||
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 8 | 12/6/2023 0:00 |
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 9 | 12/6/2023 0:00 |
My measure:
Recover =
VAR SI_date = CALCULATE( sum ('Fact - SIT'[SIT Qty]), filter ( all ('Fact - SIT'[StockAvailDate]), 'Fact - SIT'[StockAvailDate] < EOMONTH( today () ,0 )))
Here is Measure return:
| ITEM_DESC | Lost Sales Qty (Net) | DC SOH | SIT | Earliest StockAvailDate | Qty SI |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 30 | 0 | 1410 | 11/19/2023 0:00 | 0 |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 15 | 0 | 1410 | 11/19/2023 0:00 | 0 |
| DW SIP MU SPF 10-PEBBLE 30ML/1FLOZ | 3 | 138 | 5 | 12/8/2023 0:00 | 3 |
| DW SIP MU SPF 10-SHELL B 30ML/1FLOZ | 5 | 0 | 0 | ||
| CRM - ULRY TL 200ML | 3 | 79 | 3 | ||
| CRM - BFUL MAG 50ML | 2 | 217 | 2 | ||
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 8 | 12/6/2023 0:00 | 0 |
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 9 | 12/6/2023 0:00 | 0 |
| Total | 64 | 64 |
My question:
1. Why first 2 lines return to 0? I want it return to 30 & 15 as SIT colum = 1410 and available date is still less than 30 Nov
2. Sum colum Qty SI return to the min of 3 colums. How can i sum those based on the Qty SI colum.
Out come i would like to achive:
| ITEM_DESC | Lost Sales Qty (Net) | DC SOH | SIT | Earliest StockAvailDate | Qty SI |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 30 | 0 | 1410 | 11/19/2023 0:00 | 30 |
| GENTLE EYE MU REMOVER 100ML/3.4FLOZ | 15 | 0 | 1410 | 11/19/2023 0:00 | 15 |
| DW SIP MU SPF 10-PEBBLE 30ML/1FLOZ | 3 | 138 | 5 | 12/8/2023 0:00 | 3 |
| DW SIP MU SPF 10-SHELL B 30ML/1FLOZ | 5 | 0 | 0 | ||
| CRM - ULRY TL 200ML | 3 | 79 | 3 | ||
| CRM - BFUL MAG 50ML | 2 | 217 | 2 | ||
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 8 | 12/6/2023 0:00 | 0 |
| SPRM+ BRT PWR SFT CRM RF 75ML/2.5OZ | 3 | 0 | 9 | 12/6/2023 0:00 | 0 |
| Total | 64 | 53 |
TIA 🙏
2 Replies
1.Why 0 ?
I think the issue with the first two lines returning 0 is because the calculation of Si_date is not filtering the 'Fact - SIT' table by individual 'ITEM_DESC' and 'StockAvailDate' conditions. To resolve this, you need to adjust the calculation to filter the 'Fact - SIT' table based on the corresponding 'ITEM_DESC' and 'StockAvailDate' conditions for each item individually.
