cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Return the closest DATE evaluating a condition on a measure

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.

1 ACCEPTED SOLUTION
Community Support

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

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.

2 REPLIES 2
Community Support

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

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.

Super User

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.