topic Getting Last NonBlank Value in a column in DAX Commands and Tips

Getting Last NonBlank Value in a column

millercj — Thu, 19 Dec 2024 20:14:04 GMT

I have a data set that looks like this:

Date	Sales	Ratio
1/1/2024	156164	.5812
1/15/2024	16871
2/1/2024	561685
2/15/2024	468153	.6021
3/1/2024	54615313	.5011
3/15/2024	5415
4/1/2024	568763
4/15/2024	964551	.5013
5/1/2024	15156

I am attempting to return the last non-blank value in Ratio Column (in red), .5013. The Last Non-Blank of the Ratio column will not be the last row of the data set.

If I use LASTNONBLANK() I get .5812. If i use FIRSTNONBLANK() I get .6021.

How to I return the last non blank value of the ratio column?

Re: Getting Last NonBlank Value in a column

gmsamborn — Thu, 19 Dec 2024 22:35:57 GMT

Hi millercj

If you want to return the absolute last Rate for every row:

LASTNONBLANKVALUE Ratio = IF( [Sales Amount], CALCULATE( LASTNONBLANKVALUE( 'Date'[Date], MAX( 'Table'[Ratio] ) ), ALL( 'Date' ) ) )

If you want to "fill down":

LASTNONBLANKVALUE Ratio 2 = IF( [Sales Amount], CALCULATE( LASTNONBLANKVALUE( 'Date'[Date], MAX( 'Table'[Ratio] ) ), 'Date'[Date] <= MAX( 'Date'[Date] ) ) )

Let me know if you have any questions.

Getting LASTNONBLANK Value in a column.pbix

Re: Getting Last NonBlank Value in a column

Kedar_Pande — Fri, 20 Dec 2024 12:43:26 GMT

millercj

You can try:

Last Non-Blank Ratio = 
CALCULATE(
LASTNONBLANK('YourTable'[Ratio], 1),
FILTER(
'YourTable',
NOT(ISBLANK('YourTable'[Ratio]))
),
ORDERBY('YourTable'[Date], DESC)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: Getting Last NonBlank Value in a column

Anonymous — Mon, 23 Dec 2024 05:11:59 GMT

Hi All,
Firstly gmsamborn and Kedar_Pande thank you for your solutions!
And millercj ,Here is my solution I hope it helps you!

LastNonBlankRatio = VAR LastDateWithRatio = MAXX( FILTER( ALL('Table'), LASTNONBLANK('Table'[Ratio],'Table'[Date]) ), 'Table'[Date] ) RETURN CALCULATE( MAX('Table'[Ratio]), 'Table'[Date] = LastDateWithRatio )

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Getting Last NonBlank Value in a column

Poojara_D12 — Mon, 23 Dec 2024 06:56:39 GMT

Hi millercj

To return the last non-blank value in the Ratio column:

LastNonBlankRatio = CALCULATE( LASTNONBLANK('Table'[Ratio], 1), NOT(ISBLANK('Table'[Ratio])) )

LASTNONBLANK finds the last non-blank value in the Ratio column.

NOT(ISBLANK(...)) ensures blanks are ignored.

This will return 0.5013, the correct last non-blank value.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS