Minimum Product Date with Batch

[Syndicate_Admin]

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))

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.

[v-jingzhang]

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.