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:   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 ...
  • 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.

     

gmsamborn's avatar
gmsamborn
Icon for Super User rankSuper 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