Forum Discussion

mshaivitz's avatar
mshaivitz
Frequent Visitor
8 years ago
Solved

Blank/Null dates showing wrong in Esri map

I have a list of dates in a spreadsheets along with locations in the Esri map visualization. The date is a 'start date' and most of the cells are blank. Instead of showing as (blank) they are displaying the date 12/31/1969.

 

Any ideas on how to show the Null cells as either (blank) or not have them displayed in the tooltip at all if there isn't a date in there?  This goes away if I chose 'Date Hierarchy' but that is a lot of empty tool tips if I chose that option and I lose the title 'start date'.

 

Many thanks.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi mshaivitz,

     

    Based on my test, you can firstly use the formula below to create a new measure to get the "Implementation Will Begin" Date(return "(Blank)") when it is Null.

    Implementation Will Begin On = 
IF (
    ISBLANK ( MIN ( 'Table 0'[Implementation Will Begin] ) ),
    "(Blank)",
    MIN ( 'Table 0'[Implementation Will Begin] )
)

     

    Then show the measure instead of the "Implementation Will Begin" column on the Tooltips to show the Null cells as (Blank) in the tooltip if there isn't a date in there.

     

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi mshaivitz,

     

    Based on my test, you can firstly use the formula below to create a new measure to get the "Implementation Will Begin" Date(return "(Blank)") when it is Null.

    Implementation Will Begin On = 
IF (
    ISBLANK ( MIN ( 'Table 0'[Implementation Will Begin] ) ),
    "(Blank)",
    MIN ( 'Table 0'[Implementation Will Begin] )
)

     

    Then show the measure instead of the "Implementation Will Begin" column on the Tooltips to show the Null cells as (Blank) in the tooltip if there isn't a date in there.

     

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      My date is in a date data type, while this works for the blanks, the records that had a real date are shown as #0.0.00

      Do I need to conver the date back to a string? If so, what is the formula for this. TIA.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I am having the same issue when using this code

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi mshaivitz,

     

    Could you post your some sample/mock data which can help us to reproduce the issue, so that we can better assist on it? It's better that you can share a sample pbix file(with just some sample/mock data) which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

     

    Regards