Getting Last NonBlank Value in a column

[millercj]

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?

[Poojara_D12]

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 

[Anonymous · Accepted 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.

[Kedar_Pande]

@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

[gmsamborn]

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