Forum Discussion

millercj's avatar
millercj
Regular Visitor
1 year ago
Solved

Getting Last NonBlank Value in a column

I have a data set that looks like this:

 

DateSalesRatio
1/1/2024156164.5812
1/15/202416871 
2/1/2024561685 
2/15/2024468153.6021
3/1/202454615313.5011
3/15/20245415 
4/1/2024568763 
4/15/2024964551.5013
5/1/202415156 

 

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?

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

4 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    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

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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