DAX Get Second-to-LASTNONBLANKVALUE

[stalerik]

Hello,

I created a measure using the formula to get the last non-blank value in a column.

LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value]))

I would like to compare this value to the previous row, or the second last non-blank value.  Is there a formula to do this?  Thank you.

[Anonymous · Accepted solution]

Please first read this: https://dax.guide/lastnonblankvalue/

You are not using CALCULATE to wrap MAX(...), so you're most likely calculating this incorrectly, as there's no context transition under LASTNONBLANKVALUE (which is in fact an iterator).

By the way, this function is much slower than the alternatives that Marco Russo and Alberto Ferrari describe in this article: https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/

To get the second non-blank value once you've established the date of the first non-blank value you just do the same thing but this time you restrict the date table to only those days that are before the one you've already found. Easy.

[stalerik]

@Anonymous

Thank you for the articles.  I was trying the other solutions offered on this post and they did not work but I think I have a basic misunderstanding on what LASTNONBLANK and LASTNONBLANKVALUE were doing.  I was getting what looked like the right answer with my formula, but I was going about it the wrong way.  I appreciate the resources.  

[mahoneypat]

Here is one way to do it.  I split it into multiple variables to make it easier to follow.

SecondLastBlankValue =
VAR summary =
    FILTER (
        Table1,
        NOT (
            ISBLANK ( Table1[Value] )
        )
    )
VAR top2 =
    TOPN (
        2,
        summary,
        Table1[Date], DESC
    )
VAR top1 =
    TOPN (
        1,
        top2,
        Table1[Date], ASC
    )
RETURN
    MAXX (
        top1,
        Table1[Value]
    )

Regards,

Pat

[amitchandak]

@stalerik ,  Try like

calculate(LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value])), filter('Table1', 'Table1'[date] <max('Table1'[date])))

or
calculate(LASTNONBLANKVALUE('Table1'[date], MAX('Table1'[value])), filter(all('Table1'), 'Table1'[date] <max('Table1'[date]))) //or allselected , depending on need

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.