topic Re: Last "non-blank" value in a "non-sorted" column in DAX Commands and Tips

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

Toby001 — Tue, 28 May 2024 11:13:24 GMT

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

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

ValtteriN — Tue, 28 May 2024 12:41:37 GMT

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/

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

Toby001 — Tue, 28 May 2024 15:25:27 GMT

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

Many thanks for your help .. it perfectly works.