Date Slicer - does not have an option to show nulls

[Ksheetij]

Hello,

I'm trying to filter my dataset based on a date range which the user selects using a date slicer. I want to show null dates as well in the result. But I don't see any option to include null dates in any slicer which shows a date range.

I tried using custom visuals like TimeBrush, Timeline, etc. But they too filter out the nulls automatically.

I want to know if there is any way to show the null dates while specifying a date range as well.

Note: I want to display the Null Dates as blank values and not as any other date.

Any pointers to achieve this will be appreciated.

Thanks,

Ksheetij

[realvvk]

Well, this is unacceptable. I just spend several hours troubleshooting why my values are slightly off. Just to realize that by adding a date slicer, without specifically changing anything, the existance of the slicer alone eliminates records where the date is null from all visuals! This doesn't make any sense!

[v-qiuyu-msft]

Hi @Ksheetij,

The slicer visual Between mode requires the start date and end date have acutual date value. While both List, DropDown modes have option for filter Blank values. See:

Best Regards,
Qiuyun Yu

[vanessafvg]

you wouldn't be able to use the date slicer then you would need to change the data type to text because it woudln't make sense to plot nulls on a date range. 

You could also change all nulls to a dummy date like 1899/01/01 or 2050/01/01 or something where it is obvious that is refers to your  null values.

[Ksheetij]

Thanks for your inputs Vanessa.

Looks like I won't be able to use a date slicer for this requirement unfortunately.

Changing the null date to a some other date would cause confusion to the users, so I wouldn't prefer that route.

I get the required result using a filter with advanced filtering options where I get to choose "is blank". I was hoping there would be some simple way to include nulls in the date slicer, as that is more user friendly for changing the date range.

Kindly let me know if there are any alternative options for the user to filter between two dates and show null values as well.

Best,

Ksheetij

[sahilhira162]

@Ksheetij

I am looking to use date filter and I want to include NULL in my data as well. My problem is when I have a default date range it includes NULL but as soon as I enter my desired date range it removes NULL. I want a setting where it either includes or excludes NULL completely. I think we don't have that option in power BI right now, can you suggest what option you tried. Because if I include date in report/page filter option and then choose "is blank" option, it will return only blank options. I want both blank and range.  
Any help is appreciated, Thanks in advance.

Regards,
Sahil 

[anupampandey]

Hi @Ksheetij,

I would suggest you to create a calendar/date table than create a relationship between this table and table in which you are having data.  The way I create a date table is as follows:

Click the insert new table button on the ribbon and copy in below:

DateKey = CALENDAR(DATE(2012,01,01),DATE(2017,06,30))

Then I add in a new column from the ribbon for each of the following:

Year = YEAR(DateKey[Date])
Month number = MONTH(DateKey[Date])
Month = FORMAT(DateKey[Date],"MMM")
Day = FORMAT(DateKey[Date],"ddd")
Week = WEEKNUM(DateKey[Date],1)
Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)
MonthY = FORMAT(DateKey[Date],"MMM")&" " &DateKey[Year]
Day number = DAY(DateKey[Date])
Financial year = IF(DateKey[Month number]>6,DateKey[Year]+1,DateKey[Year])
Financial week = IF(DateKey[Month number]>6,DateKey[Week]-26,DateKey[Week]+26)
Total days = DAY(DATE(DateKey[Year],DateKey[Month number]+1,1)-1)
Financial month number = IF((DateKey[Month number]-6)<=0,DateKey[Month number]+6,DateKey[Month number]-6)
Financial month = FORMAT(DateKey[Date],"MMM")
Index = CALCULATE(COUNT(DateKey[Date]),ALL(DateKey[Date]),FILTER(DateKey,DateKey[Date]<=EARLIER(DateKey[Date])))
Monthy number = DateKey[Year]&DateKey[Month number]

Create a day slicer from calendar date instead of data table. I hope this will solve your problem.

Cheers,

Anupam

[Ksheetij]

Hi Anupam,

I tried creating a date dimension/table as you suggested.

This gives me the same result whether I use the date from the date table or the date in the fact table.

I'm not sure how the null dates would get displayed on joining to a date table.

Maybe I'm missing out on something here. Kindly advise.

Best,

Ksheetij

[anupampandey]

Hi Ksheetij,

Can you please share me the sample file? I will see into and revert you if I can find out the solution.

Regards,

Anupam