Forum Discussion

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver II
6 years ago
Solved

How to get latest no blank value

Hi There,

 

I have ID's, Dates and Comments as per below (pretending that the latest date is 3/06/2020) I need to get the latest non blank comment to populate in my results column (blue text)

 

DateIDCommentLast available Comment
29/05/20201.AHelloHello 1

1/06/2020

1.AHello 1Hello 1
2/06/20201.A Hello 1
3/06/20201.A Hello 1

 

I need the results to appear as they do in the 'Last available comment' column? How do I write this calculated column?

 

Thanks!!

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Hayleysea 

    try a measure

    Last available Comment = 
var _lastDate = CALCULATE(MAX(Table[Date]), Table[Comment] <> BLANK() )
RETURN
CALCULATE(LASTNONBLANK(Table[Comment], 1), Table[Date] = _lastDate  )

4 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Hayleysea 

    try a measure

    Last available Comment = 
var _lastDate = CALCULATE(MAX(Table[Date]), Table[Comment] <> BLANK() )
RETURN
CALCULATE(LASTNONBLANK(Table[Comment], 1), Table[Date] = _lastDate  )
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Hayleysea , Try like

    calculate(LASTNONBLANKVALUE(Table[date],max([Comment])), filter(all(table), table[date]<=max(table[date])))

    • Hayleysea's avatar
      Hayleysea
      Icon for Resolver II rankResolver II
      6 years ago

      Thank you but I got this error: A circular dependency was detected: Table[comment].