Forum

Slicer / Filter bla...
 
Notifications
Clear all

Slicer / Filter blank dates

2 Posts
2 Users
0 Reactions
227 Views
(@ron007)
Posts: 2
Active Member
Topic starter
 

I've participated in several of your free Pivot / Dashboard webinars. Really good stuff!

 

I have a column of dates, with some blanks. 

In a pivot table I can slicer/filter on specific date values to provide counts.  But when I want to count "blank" dates nothing happens. I see "blank" in the date column slicer, I can select it, but nothing is counted.

Googline I found a suggestion to convert the blank cells to a value in a helper column, ie:

=IF(IsBlank(a1),"blank",a1)

This works if it is a column of text values, but not for dates.

I also tried creating a text helper column of the date, ie

=text(a1)

The only thing I found that worked specifically for a date column was to enter a dummy value, ie 1, into the blank cells.  Once it was converted to 01-00-00 (dd-mm-yy) and another time to 01-01-00.  After refreshing the pivot, I could then count these dummy values

Is this a known "feature". or a "FAD" (Failing As Designed)?

Is there some other workaround to count blank dates?

 
Posted : 09/05/2019 9:14 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Ron,

You can write a Power Pivot DAX measure to count blanks:

=COUNTBLANK([YourDateColumnName])

Mynda

P.S. great to know you enjoyed the webinars 🙂

 
Posted : 09/05/2019 7:36 pm
Share: