Last "non-blank" value in a "non-sorted" column

[Toby001]

Dear community,

In power bi I am using as an example a very simple table with 2 columns (date, value) and need a measure to collect the last non-blank value (0.65) for a card. Maybe I am wrong, but my understanding is that the LASTNONBLANK function works with a "sorted" column only.

Is there any function in the Dax measure to identify the last non-blank value in an "non-sorted" column (such as in the value example)  .. ?

date	value
01.01.2024	0.89
01.02.2024	1.34
01.03.2024	0.65
01.04.2024
01.05.2024

Appreciate your ideas and input. Many thanks ..

Best

Toby

[Toby001]

Looks like I still have to learn some tricks .. :).

Many thanks for your help .. it perfectly works.

[ValtteriN · Accepted solution]

Hi,

You can achive the desired effect easily e.g. like this:

Measure 27 =

var _mdate = MAXX(FILTER('Table (40)',not ISBLANK([value])),[date])

RETURN

CALCULATE(MAX([value]),ALL('Calendar'),'Calendar'[date]=_mdate)

You can also use the original table instead of calendar like in this example:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/