Include empty date values in table when changing date slider

[poojithas]

Hi All,

I have a field called "NEXT_REVIEW_DATE" which is of date datatype. I am using this field as a range slicer.

There are few records with empty  date value for "NEXT_REVIEW_DATE"  .

When ever a user changes the slider, they should be seeing the records wtith empty date as well along with the selected dates,.

Inital View with blank dates:

Now, in the eg. below when the user changes the date,the blank records are disappearing.They should be able to see data from 1/23/2022 to 3/1/2023 along with the blank entities.

Could someone please let me know how to acheive this.

Thank you.

[some_bih]

Hi @poojithas I think that the best would be to change your slicer to vertical list.This solution ensure user interaction is properly applied together with Blank values. I hope this help

[some_bih]

Hi @poojithas I think that the best would be to change your slicer to vertical list.This solution ensure user interaction is properly applied together with Blank values. I hope this help

[poojithas]

@some_bih  : User is looking to have a date filter with range and a drop down may not be a best option for them as the no.of dates are too many.

[some_bih]

Hi @poojithas I agree, still to provide dates in range and BLANK at same time the best is to use slicer on interaction to include blanks otherwise, there could be ambigious results.

[some_bih]

Hi @poojithas I inserted into Excel your data and impor into Power BI.

During transformation in Power Query I choose data type for dates columns as : type date. Result is visible below. In table visual when I change date period, dates from period AND Blanks are visible.

Please try to change your data type to type date and check interaction with slicer. I hope this help.

[poojithas]

Hi @some_bih : I have set the data types as in the report. Blank dates are visible only when the left side of the slider is unchanged and right side slider is changed.

We would like to see blank records when right or left slider is changed by user.
Inital View when the slider is unchanged:

View when right side slider is changed, We get blank dates as well:

View when left side slider is changed, blank values are disappeared

[some_bih]

Hi @poojithas , check 1) Uploading files on the link 1) Uploading files https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-... 

[some_bih]

Hi @poojithas please share your pbi file. Link from community is not one I participated.

[poojithas]

@some_bih  : As I am having limitations to access to upload files outside f client network, I am pasting sample data here.

NEXT_REVIEW_DATE is the field used in the slicer.
Sample screenshot:

Whe, I change the slider , along with the selected dates, I should also be able to get the records with no dates as well.

Hope these details will help to replicate the issue.

KEY	CT_DATE	STATUS	Duration	DUE	TOTAL	EXPIRES	% USED	LAST_REVIEW_DATE	NEXT_REVIEW_DATE
200635194	6/15/2022 0:00	Active	7	0	109551.19	5/30/2023 0:00	73.03%
601139617	6/15/2004 0:00	Active	14	0	1510.28		75.51%
601484357	7/12/2010 0:00	Active	21	0	36857.27	8/28/2022 0:00	73.71%		2/28/2023 0:00
601524082	5/5/2011 0:00	Active	21	243.91	396.13		99.03%
601682696	4/29/2014 0:00	Active	21	0	27885.88		79.67%		2/28/2023 0:00
601733618	11/6/2014 0:00	Active	21	0	17637.81		70.55%		6/30/2022 0:00
601970197	5/23/2017 0:00	Active	14	0	18503.89		74.02%		5/31/2022 0:00
601981626	6/21/2017 0:00	Active	14	0	39572.5	11/7/2022 0:00	79.15%		2/28/2023 0:00
602075015	3/1/2018 0:00	Active	14	0	33399.61		74.22%	9/16/2022 0:00	1/31/2023 0:00
602075283	3/5/2018 0:00	Active	14	0	18270.34		73.08%		3/31/2023 0:00
602304971	9/10/2019 0:00	Active	14	0	21700.97		86.80%		1/31/2023 0:00
602322006	11/18/2019 0:00	Active	21	0	63385.44		97.52%		8/31/2022 0:00
602342303	3/2/2020 0:00	Temporary Close	14	0	5105.68		102.11%		7/31/2021 0:00
602357379	5/15/2020 0:00	Permanent Close	21	38524.93	75293.37	5/23/2022 0:00	100.39%		3/31/2022 0:00
602373348	8/24/2020 0:00	Active	14	0	1509.05		75.45%		6/30/2022 0:00
602424344	5/24/2021 0:00	Active	21	0	59300.76	6/30/2022 0:00	79.07%
602456321	10/13/2021 0:00	Permanent Close	14	2193.95	2263.95		113.20%
602469194	12/14/2021 0:00	Active	14	0	1408.9		70.45%	11/30/2022 0:00	3/31/2023 0:00
602481285	2/17/2022 0:00	Active	14	0	3536.07		70.72%		3/31/2023 0:00
602522226	8/2/2022 0:00	Temporary Close	21	84255.57	84290.57	1/13/2023 0:00	84.29%
602530958	9/2/2022 0:00	Permanent Close	14	1963.21	2033.21		101.66%
602532487	9/5/2022 0:00	Active	21	0	36069.97		72.14%
602540988	10/7/2022 0:00	Active	14	0	4129.52		82.59%
602543114	10/13/2022 0:00	Active	14	0	3656.91		73.14%
602548148	11/8/2022 0:00	Active	14	0	3657.48		73.15%
602561281	1/18/2023 0:00	Active	14	0	1412.03		70.60%
602564002	2/1/2023 0:00	Permanent Close	14	2045.93	4262.6		85.25%
602568822	2/27/2023 0:00	Permanent Close	14	4426.01	4461.01		89.22%
602568871	2/27/2023 0:00	Permanent Close	14	2216.08	2286.08		114.30%

[poojithas]

Hi @some_bih - I have a sample test pbix file. Could you suggest how I can share the file here

[some_bih]

Hi @poojithas please check community link https://community.fabric.microsoft.com/t5/Desktop/OR-include-blank-values-in-date-slicer/m-p/959529 I hope this help

[poojithas]

@some_bih  - Thanks for the link but I am unable to fix the issue.  Below is the code from  the link provided.  I have tried to replace the [Date] field from the code with the [NEXT_REVIEW_DATE] in my case . 

Also, I was unable to get to how Slicer[Allowblank] is used. Could you please share a sample pbix file if possible.

measure =
var _min = minx(allselected(Date,Date[Date])
var _max = minx(allselected(Date,Date[Date])
var _maxval = if(isfiltered(Slicer[Allowblank]),max(Slicer[Allowblank]),blank())
return
if(_maxval ="Allowblank",
calculate(sum(table[Value]),filter(all(Table[Receive Date]),Table[Receive Date]>=_min && Table[Receive Date]>=_max && isblank(Table[Receive Date])))
,
calculate(sum(table[Value]),filter(all(date),Date,Date[Date]>=_min && Date[Date]>=_max))
)

[some_bih]

Hi @poojithas check this features in Power BI https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data I hope this help

[poojithas]

Hi @some_bih  : I tried checking this option and I am still not able to see any data with null dates when the dates in the slicer is changed.

Please note that we do have data in the dataset for the missing dates, just that the date field is empty.

There are other fields as well in the report. The above is a sample data with 2 fields.