Unexpected result of 'lastnonblank' formula

[owais]

Hi

I am getting an strange or unexpected result in calculated column in 'lastnonblank' formula. Bilow is the picture describing the problem.

'Last update date' column is also a calculated column using last date formula but same formula is resulting wrong value in next column. How I can fix this error?

n.b. Relation between both table is 1 to many.

[v-qiuyu-msft · Accepted solution]

Hi @owais,

Please create a calculated column use LOOKUPVALUE( ):

current status = LOOKUPVALUE('updates'[Update Status],'updates'[Project Name],'Table1'[Project Name],'updates'[Update Date],'Table1'[last update date])

Best Regards,
Qiuyun Yu

[owais]

Hi @v-qiuyu-msft

Thanks for perfect reply. Its working with lookup. But could you explain what was wrong in my formula? and another thing, if there are 2 or more entries in same date for same project, which wone it will take?

Thanks

[v-qiuyu-msft]

Hi @owais,

The LASTNONBLANK() function is used for returning the last value in the column, column, filtered by the current context, where the expression is not blank.

Regarding the calculated column:

Column = LASTNONBLANK('updates'[Update Status],LASTDATE('updates'[Update Date]))

As 'updates'[Update Status] is TEXT, it will sort 'updates'[Update Status] values based on the first character within each project. So that's the issue why you didn't get desired results.

Best Regards,
Qiuyun Yu