Forum Discussion

n_b_'s avatar
n_b_
Regular Visitor
5 years ago
Solved

Return a string with LASTNONBLANK?

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 h...
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    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

dedelman_clng's avatar
dedelman_clng
Community Champion
5 years ago

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

  • n_b_'s avatar
    n_b_
    Regular Visitor
    5 years ago

    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_'s avatar
      n_b_
      Regular Visitor
      5 years ago

      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's avatar
        dedelman_clng
        Community Champion
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      dedelman_clng
      Community Champion
      4 years ago

      Hi Anonymous  - 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