Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Finding date for last non zero value in a table

I'm looking to build a measure that returns the last date where an hour is forecast.  I have to have 0's in the forecast for a burndown chart measure I'm using otherwise I get odd spikes.   Current...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please try the measure below and see if the result achieve your expectation.

    Measure = 
var a = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Hours]<>0))
return
IF(MAX('Table'[Date])=a,MAX('Table'[Hours]),BLANK())

    Result would be shown as below.

     

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks Parry2k - First date records the first 0 in the list, I'm after the item before the last 0 in the data set,

 

This seems to work though, so I'm pretty sure you put me on the right track!

 

CALCULATE(LASTDATE(BUDGET_DATA[Date]),KEEPFILTERS((BUDGET_DATA[Hours]<>0)))
 
hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
6 years ago

Hi Anonymous ,
I think your original max function should work with an added filter context. Try this as a calculated column:

CALCULATE(MAX([Date]), FILTER(BUDGET_DATA, [Item] = EARLIER([Item]) && [Hours] <> 0))