Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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.
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
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.
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |