Finding date for last non zero value in a table

[Anonymous]

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.

Currently using 

Burndown = A_F_COMBINED_DATA[ForecastTotal]-calculate(sum(A_F_COMBINED_DATA[Hours]),FILTER(ALL(A_F_COMBINED_DATA[Date]),A_F_COMBINED_DATA[Date] <= Max (A_F_COMBINED_DATA[Date])))

ForecastTotal = calculate(SUM(A_F_COMBINED_DATA[Hours]),ALL(A_F_COMBINED_DATA[Hours],A_F_COMBINED_DATA[Date]))

I then use this:

ForecastMaxDate = Max(A_F_COMBINED_DATA[Date])

But will get the last date in the whole data set as 0 is read as a positive.

What I'm looking for is the last non-zero value and return the date associated with it.

So for the below example, A would be 18/12/19

B would be 20/12/19.

Any ideas?

Item	Date	Hours
A	18/12/19	1
B	18/12/19	0
A	19/12/19	0
B	19/12/19	2
A	20/12/19	0
B	20/12/19	1

A	23/12/19	0
B	23/12/19	0

[v-jayw-msft · Accepted solution]

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.

[parry2k]

@Anonymous add following measure to get the date

First Date = 
CALCULATE( 
FIRSTDATE ( hurs[Date] ),
KEEPFILTERS( hurs[Hours] = 0 ) 
)

[Anonymous]

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]

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