Return Date fulfilling Criteria

[monsoonnut]

Hi

I am stumped on how to construct a measure that will return a YearWeek value dependent on 'another' measure.

The 'other' measure is 'Projected Closing Stock': '(current stock + cumulative orderbook(future)) - cumulative forecast(future)' which is calculated at weekly (not daily) level. I am trying to return the week where the projected closing stock falls below zero.

I have a custom dates table (daily level) which is marked as a dates table in the model.

I'm thinking FIRSTDATE is the way to go but can't get it to work in the expected, or any way really.

Any ideas, suggestions appreciated.

Thanks

M

 

[monsoonnut]

I Worked it out, GFITW Strikes again!

Projected Week OOS:=CALCULATE(Min(DimDate[YearWeek]),ALL(DimDate),FILTER(ALL(DimDate),'Key Line OS Orders'[Total Projected Closing Stock]<=0))

M

 

[akice]

Cool you solved the problem. One small note though. The second parameter to CALCULATE - ALL(DimDate) - is redundant and not necessary and can be removed from the formula.

 

[nikkollai]

Please correct me if I am wrong but it appears his formula dos not work

DAX does not allow to build table[column] expression inside FILTER expression (below marked in bold). You would need to mix it with VALUES() to bring in column "Total Projected Closing Stock"

Projected Week OOS :=
CALCULATE (
MIN ( DimDate[YearWeek] ),
FILTER (
ALL ( DimDate ),
'Key Line OS Orders'[Total Projected Closing Stock] <= 0
)
)

 

[Matt Allington]

Regarding the reduntant all(dimdate), my guess is monsoonnut learnt his pattern from Rob Collie. Rob came clean in 2013 that he had been communicating this formula with the redundant all

http://www.powerpivotpro.com/2013/11/the-gfitw-loses-an-all/

 

[Matt Allington]

Please correct me if I am wrong but it appears his formula dos not work

DAX does not allow to build table[column] expression inside FILTER expression (below marked in bold). You would need to mix it with VALUES() to bring in column "Total Projected Closing Stock"

table[column] is allowed as long as 'table' is the same table as the first parameter in filter. In is case, I am guessing this part of the filter formula is actually a measure. Best practice is to not specify the table name of a measure to avoid this exact confusion.