How to get the last value in a table in Power BI using DAX

Question

I've spent way too much time trying to figure this out, and even though I looked at several sites, I couldn't get this to work, and I know it's probably dead simple.

Using Power BI Desktop (September 2023), I'm trying to get a value from the last row of a table.

My source is a basic Excel with one table inside, three columns, first a Date, then two columns with numerical values:

Date	OK	NOK
17/01/2024	123	2
18/01/2024	234	6
19/01/2024	56	9
20/01/2024	1	11
21/01/2024	23	34
22/01/2024	54	23

In the Power Query Editor, I see that the date column is automatically recognized. To make sure the order is the one I want I force Sort by Date ascending.

I then display a table view of my data, as well as a card with the latest value from the NOK column. For this I created a new Measure, Last NOK that is defined like so:

Last NOK = LASTNONBLANK(Table1[NOK], Table1[NOK]). I've also tried Last NOK = LASTNONBLANK(Table1[NOK], 1) which should be equivalent in my case.

I hope you agree that I should see the value 23 displayed in the card on the next screenshot. I have no idea why it shows 9 instead. I had tried this with some more real-life data earlier, and there too, a value from somewhere inside the column was selected (always the same).

I had found this page which seemed to take the black magic out of this function, but still I must be doing something very simple very wrong. 🤷‍♀️

Just to push the question a bit further I created equivalent measures for the other two columns:

I should see 23, 54, and 22/01/2024, but only the date value is correct. Any help is greatly appreciated!

Answer 1

Power query doesn't affect the sorting in Power BI. The measure you want is:

Measure = LASTNONBLANKVALUE(Table1[Date], SUM(Table1[NOK]))