Need a measure to select a Week

[airwolf39]

Hi Folks,

The first part of my  probelm was fixed. I can now find the date when inventory goes to 0 using this forumula:

Now i need a measure that will select the week that the "No Inv Date" falls in (in the above case it is Week 2).

Here is the measure that i am trying to use to make this work:

I am trying to use the 'No Inv Date' to set the max of the Due date in the date table and then pick the last non blank of the Week column... which should be '2'. Any ideas how to modify the above measure to remove the error?

Thanks,

Brian

[PaulDBrown]

@airwolf39 

Try:

Week number  = WEEKNUM([No inv Date])

[airwolf39]

Great suggestion, but I failed to mention that 'week' is a calculated column. If you look at my graph, you will see that 'week' is really 'week from today'. That is why there is a week 0 (these are the dates from yesterday and before).

[PaulDBrown · Accepted solution]

@airwolf39 

Ok, try:

week num = LOOKUPVALUE(Date Table [Week], Date Table [Date], [No inv Date])

[airwolf39]

Oh my goodness, i didnt realize that I could use lookupvalue in a measure... i thought it was restricted to creating calculated columns... what a game changer! Thank you!!

[airwolf39]

Post edited with new 'Find Week" issue

[lbendlin]

use

..., if(sumx('Master Spec 5',[Ending Inv_C])=0,1,0))

[lbendlin]

Use CALCULATE with a value filter

something like CALCULATE(FIRSTDATE(dates[date]),sum(Inventory[Units])=0)