Date Filters with NULL values

[sahilhira162]

Hi 

I am trying to create a report where, I need to filter by start date and end date, there are few record with NULL data in start and end date column.

These start dates and end dates are automatically set, as they pick the min and max dates. This is also showing the NULL records, but if I see the reports, its confusing, coz it looks like that its showing data only between those 2 dates.
Is there any way where I can changes in dates too NULL so that its clear that its starting from NULL to a particular date.

Any help is appreciated, thanks in advance.

Regards,
Sahil

[v-caliao-msft]

@sahilhira162,

Please refer to the link below.

1. Create two tables.
   StartDateSlicer = CALENDAR(DATE(2015,1,1),DATE(2016,6,30))
   EndDateSlicer = CALENDAR(DATE(2016,7,1),DATE(2017,12,31))
2. Use the date in new create tables in your startdate and enddate slicer.
3. Create a measure in your originale table
   Measure =
   var minstartdate = MIN(StartDateSlicer[Date])
   var maxstartdate = MAX(StartDateSlicer[Date])
   var minenddate = MIN(EndDateSlicer[Date])
   var maxenddate = MAX(EndDateSlicer[Date])
   return
   if((ISBLANK(MAX(Employee[StartDate]))||ISBLANK(MAX(Employee[EndDate]))||((MAX(Employee[StartDate])>=minstartdate&&MAX(Employee[StartDate])<=maxstartdate)&&MAX(Employee[EndDate])>=minenddate&&MAX(Employee[EndDate])<=maxenddate)),1,0)
4. Use this measure in your visual as a filter.
   
5. 

Regards,

Charlie Liao

[sahilhira162]

@v-caliao-msft
Thanks for your response, but the filters are not working properly. The problem I am facing is, when we have a default date range in filter it considers NULL values also, but as soon as we change the date to desired dates, it removes NULL immediately. Is there any way that we can include NULL even if we filter the dates to desired dates.
Or can we have option in Date Filter Range which specifies it includes NULL.
Kindly suggest what to do.

Regards,
Sahil

[RobinSarac]

This is a glaring problem with the slicers, in my opinion.  Blank / NULL / missing dates are a legitimate value.  The ability to include hem with a selected date range is essential.

[Greg_Deckler]

Can you filter the null's out of your data? Or, can you replace the nulls in your data load with something like 1/1/1900?

[sahilhira162]

Hi @Greg_Deckler

Thanks for your response.

No I can't filter them or change them to 1990. Some of the projects are just announced and start date, end date are not yet decided for them. But I want to see those projects in my report. I was confused, how can I make it visible in my report that it includes those NULL data as well. Coz what looks from the date filter is that the report contains data only from particular range only. I was hoping that in place of starting date, it can be blank so that user can understand easily that it contains NULL values as well.

Regards,
Sahil 

[Greg_Deckler]

Add a text box "Includes Null Values"?

[sahilhira162]

Any other alternative.?