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.

 

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?

 

ItemDateHours
A18/12/191
B18/12/190
A19/12/190
B19/12/192
A20/12/190
B20/12/191
A23/12/190
B23/12/190

 

 

 

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.

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous add following measure to get the date

     

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

     

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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.