Find two last non-blank values in column

[SteveWave]

Dear Community,

I simply need the find the last & previous non-blank value in the [Value] column (see image in red) and get the difference.

Actually, I tried and got the last value of 210 T, but struggled to find the previous one.

Trend = VAR _last =

MAXX( FILTER('Table', NOT ISBLANK([Value])) )

RETURN CALCULATE(

MAX('Table'[Value]),

ALL( 'Table'), 'Table'[Value] = _last )

Many thanks for your ideas.

[Anonymous]

Hi @SteveWave ,

How is the situation now? If the problem has been solved, please accept replies you find helpful as solutions. If not, you can try this DAX to get the value of the penultimate row and the value is not empty, and alternatively, you can change the value of the ordernumber to get the value anywhere you want.

Measure = 
var ordernumber = 2
var ordertable = ADDCOLUMNS(
        'Table',
        "Rank", 
        RANKX(
            FILTER('Table',NOT ISBLANK('Table'[Value])),
            'Table'[Date],
            ,
            DESC,
            DENSE
        )
    )
    RETURN
    CALCULATE(MAX('Table'[Value]),FILTER(ordertable,[Rank]=ordernumber))

Best regards,

Mengmeng Li

[ThxAlot]

[Bibiano_Geraldo · Accepted solution]

HI @SteveWave ,

To find the difference between the last and previous non-blank values in the [Value] column, you’ll need to retrieve both values separately and then calculate the difference. Here’s a DAX measure that should help:

Trend = 
VAR LastValueDate = 
    MAXX(FILTER('Table', NOT(ISBLANK('Table'[Value]))), 'Table'[Column1])
VAR LastValue = 
    CALCULATE(MAX('Table'[Value]), 'Table'[Column1] = LastValueDate)

VAR PreviousValueDate = 
    MAXX(
        FILTER(
            'Table', 
            NOT(ISBLANK('Table'[Value])) && 'Table'[Column1] < LastValueDate
        ), 
        'Table'[Column1]
    )
VAR PreviousValue = 
    CALCULATE(MAX('Table'[Value]), 'Table'[Column1] = PreviousValueDate)

RETURN 
    IF(NOT(ISBLANK(LastValue)) && NOT(ISBLANK(PreviousValue)), LastValue - PreviousValue, BLANK())

As you can see, in my sample the result was 60 comming from diference of 210-150:

Make sure to replace table and columns names with your owns.

And if this post help you, please consider to mark it as solution and give a Kudo.

Thank you

[lbendlin]

Use TOPN(2,...  filter on the value column and sort on the date column.

Then convert the resulting table to your final result