cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stalerik
Helper II
Helper II

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

@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
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@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.



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors