I'm trying to build a measure to calculate the number of items below the min or above the max stock level.
I have a lot of tables, but the important ones for this issue are:
- dSAP_MARC: Center/unit code (Cen.), SKU code (Material), minimum stock level (Estq.seg.), maximum stock level (Estoque máximo) and Inicial date (date from when parameters are valid);
- fSAP_MB51: Table with all stock movements, used to calculate daily stock level. Here I have columns Center/unit code (Cen.), SKU code (Material) and moviment date (Dt.lçto);
- dCalendar: Calendar table, used for relationships between dates;
- dSAP_MARA: SKU table, used for the relationship between SKUs;
- dCentro: relationship between Center/unit code.
I need to calculate the stock level status (above max, below min, zero and Ok) for every center, material and date. The desired stock level (from dSAP_MARC) varies over time, and it reflects on the calculated stock level status.
Stock level, calculated from fSAP_MB51:
Stock parameters, from dSAP_MARC:
The desired result of the comparison between tables fSAP_MB51 and dSAP_MARC:
How can I do that? I'd tried to do it using virtual tables... But It doesn't work.
