Return a string with LASTNONBLANK?

[n_b_]

I need to build a dashboard in which the last non-blank item in each column is displayed. This is super easy to do for number columns but I can not figure out how to do it for the text columns. Any help would be very much appreciated!

For the number columns I am using this measure, which works great:

Category - Code (most recent) = 
LASTNONBLANKVALUE (calendar[Date], SUM(  mainTable[Category- Code] ))

But this next measure is as close as I am able to get for the text columns. Unfortunately, the filter by MAX date function does not seem to be doing anything, and the LASTNONBLANK seems to be (if I am understanding correctly) sorting the category by itself (alphabetically?) and giving me the last non blank that starts with a letter closest to the end of the alphabet instead of the one with the latest date. I am stumped.

Category - Name (most recent) = 
    CALCULATE (
        LASTNONBLANK (mainTable[Category - Name], 1 ),
        FILTER (
            ALLEXCEPT ( mainTable, mainTable [Category - Name] ),
            MAX(calendar[Date]
        )
    ))

Thanks for looking and for any advice!

[dedelman_clng · Accepted solution]

Hi @n_b_ - try using

LASTNONBLANKVALUE( Calendar[Date], MAX( maintable[Category - Name] ) )

You should then not need the additional FILTER statement.

Hope this helps

David

[kterr]

Hey! I used this solution to generate the last nonblank value for a column using date. The DAX code is as follows: 

LastValue = LASTNONBLANKVALUE(Sheet1[Date Time],MAX(Sheet1[Business Data Value]))

The issue I am having is my Date Time column is formatted as "3/14/01 01:30:55 PM" and yet the formula will not retrieve the last non blank value if it is after 12:00pm. Original data was formatted "3/14/01 13:30:55" and I thought switching the Date Time column to this format "3/14/01 01:30:55 PM" would help but I am still only getting the last value before 1:00pm or 13:00. What can I do to fix this?

I should also note that any data whose last value is prior to 1:00pm or 13:00 is showing the correct last Business Data Value.

Thank you so much!

[dedelman_clng]

Hi @kterr  - I would suggest you start a new thread with your particular issue, as it will get more views than responding to a post that is already marked "solved". In that new post you should give some sample data and/or your pbix so that the community can have a better look at what you're trying to accomplish.

ALso see this post: How-to-Get-Your-Question-Answered-Quickly for more tips on how to present your issues for fastest resolution.

Hope this helps

David

[n_b_]

Thank you so much @dedelman_clng! That worked perfectly. That is so much simplier and cleaner than what I was trying to do.

[n_b_]

Actually, that seems to have lost the "last non-blank" functionality. It now shows the most recent entry for the column but if there is no entry for todays date for that item, instead of displaying yesterday's entry, it just displays the blank.

[dedelman_clng]

@n_b_  - Can you share your pbix? Scrub any sensitive data you might have.  For my simple example I get the correct value. Note the date slicer including the last day that has blanks for its values.

David

[n_b_]

Thank you @dedelman_clng!

Letting me know that that logic should be working, meant that I could shift my focus to look at how I was implementing it and also at my data. I found that within my data, in the text columns, the the blank rows were blank but they were not null so LASTNONBLANK was not recognising them as truely blank. I did a "replace values" at the Power Query level on these columns to replace [an empty field] with null, and now everything works. 

Thank you again for all your help!