Forum Discussion

stalerik's avatar
stalerik
Icon for Helper II rankHelper II
5 years ago
Solved

DAX Get Second-to-LASTNONBLANKVALUE

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's avatar
    Anonymous
    5 years ago
    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    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's avatar
      stalerik
      Icon for Helper II rankHelper II
      5 years ago

      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.