Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Minimum Product Date with Batch

Dear

Along with greeting you, I would like to request your collaboration in a dilemma that blows my mind and I have not managed to solve.

I am trying to find the closest date to expire per product, but each of them has N lots, and this has complicated the calculation.

I show them in 2 visual tables to explain them better. "vctomin" is the measure I am using to look for the minimum date. In the first table when using the fields products with their respective lot brings me each of the expiration dates per lot, but when removing the lot field I need to show the closest date of the product to expire, but by doing this it shows me what is indicated in the second table.

This is the formula I am using, which I have modified in many different ways without getting the result I need.

CALCULATE(MIN(Stock[FechaVcto]),
FILTER(
ALL(calendar),
calendar[Date] <= Max(calendar[Date])),filter(all(Measurement), Measurement[Stock] > 0))

Captura de pantalla 2022-06-09 115706.jpg

My idea is that in the field indicated by the arrow show the closest date to expire, in the case of the example it would be 16-08-2022.

Thank you very much in advance.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Gsv 

     

    You can try this measure

    Closest date to expire =
CALCULATE (
    MIN ( Stock[FechaVcto] ),
    ALLSELECTED ( 'Stock'[Lote] ),
    Measurement[Stock] > 0
)

    Or

    Closest date to expire =
CALCULATE (
    MIN ( Stock[FechaVcto] ),
    ALLEXCEPT ( 'Stock', 'Stock'[Producto], 'Stock'[GLOSA] ),
    Measurement[Stock] > 0
)

     

    If they don't work, can you please provide some mock data to show the tables as well as relationships among tables? 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.