Return the closest DATE evaluating a condition on a measure

[Anonymous]

Hello,

I am looking for to return the closest date for which a measure that I evaluate is below zero :

I evaluate the evolution of a stock level for different materials and I wish to return the first date on which one of the materials is with a stock value below zero. I tried to use FIRSTDATE and LASTDATE but without success : 

ShortageDate = CALCULATE(FIRSTDATE(DateTable[Date]), FILTER(DateTable, [Real Stock] < 0))

With this expression it returns the date on which the aggregation of all the stocks for the different materials are going below zero. 

Thanks in advance for your help

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You need to put the material column as a slicer,and try this measure:

ShortageDate =
MINX (
    FILTER (
        ALL ( DateTable ),
        [Real Stock] < 0
            && [Material] = SELECTEDVALUE ( Material )
    ),
    [Date]
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

It would be better if i could see how data looks like. 

But I would imagine this measure 

Measure = CALCULATE(MAX('Table'[Current Date]), FILTER('Table','Table'[Stock] = 0))