Forum Discussion

axk180022's avatar
axk180022
Icon for Helper II rankHelper II
5 years ago
Solved

Find the latest 2 dates with existing value

  I have this table, I need just filter the 2 latest dates for each inventserialID having an existing Generator hours.    2 LAST DATES = Var _invent = FIRSTNONBLANK('Table'[INVENTSERIALID],...
data protection
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi axk180022 

    You can refer to the measure below .

    2 LAST DATES =
Var  _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),FILTER(ALLEXCEPT('Table','Table'[INVENTSERIALID]),'Table'[Generator hours]<>0))
Var  _2ndlatest = CALCULATE(max('Table'[CALCULATIONDATETIME]),FILTER(ALLEXCEPT('Table','Table'[INVENTSERIALID]),'Table'[Generator hours]<>0 && 'Table'[CALCULATIONDATETIME]<_latestdate))
RETURN
IF(SELECTEDVALUE('Table'[CALCULATIONDATETIME]) IN {_latestdate,_2ndlatest},SELECTEDVALUE('Table'[CALCULATIONDATETIME]))

    And the final result is as shown :

    Best Regards

    Community Support Team _ Ailsa Tao

    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
5 years ago

Hi axk180022 

You can refer to the measure below .

2 LAST DATES =
Var  _latestdate = CALCULATE(max('Table'[CALCULATIONDATETIME]),FILTER(ALLEXCEPT('Table','Table'[INVENTSERIALID]),'Table'[Generator hours]<>0))
Var  _2ndlatest = CALCULATE(max('Table'[CALCULATIONDATETIME]),FILTER(ALLEXCEPT('Table','Table'[INVENTSERIALID]),'Table'[Generator hours]<>0 && 'Table'[CALCULATIONDATETIME]<_latestdate))
RETURN
IF(SELECTEDVALUE('Table'[CALCULATIONDATETIME]) IN {_latestdate,_2ndlatest},SELECTEDVALUE('Table'[CALCULATIONDATETIME]))

And the final result is as shown :

Best Regards

Community Support Team _ Ailsa Tao

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