Date Slicer - Don't allow users to select dates outside of available dates

[Anonymous]

Hi,

Is it possible to Not allow selection of dates beyond those available in dataset?

E.g. if available data is for last 3 months (e.g. Jan, 2020 to Mar, 2020), then after selecting "Between" in date slicer, users should Not be able to click on From Date field and select Nov 2019. This would give the false impression that the data displayed is from Nov 2019 to Mar 2020

[amitchandak]

@Anonymous , Not sure I got it completely. But typically if you are using date calendar, then generate it from min to max of you Main table it will allow selection in that date range only

Calendar = calendar(Min(Table[Date]),Max(Table[Date]))

[Anonymous]

@amitchandak, a proper date table always covers full years, not only the span from the min to the max date in the data set.

Hence the question.

Best
D

[Anonymous]

Here's the solution. Please note that the slicer only displays dates from the Calendar that are between the min and max dates found in the FactTable.

Best

D

[Anonymous]

Hi D,

Just tried your solution.

Unfortunately, the users still seem to have a choice of clicking on "From Date" section in the slicer i.e. "4/1/2020" and scrolling back in the calendar to an older date Or clicking on "To Date" section i.e. "6/10/2020" to scroll to a future date using the calendar pop-up.

Is it possible to blur out dates which do not lie between From and To dates?

or Not allow users to scroll through dates which are not in the range of data?

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

It is designed by default, date range slicer and number range slicer has the same limitations, 

please refer to this:

Limitations and considerations

The following limitations and considerations apply to the numeric range slicer:

• The numeric range slicer filters every underlying row in the data, not any aggregated value. For example, let's say that you use a Sales Amount field. The slicer then filters each transaction based on the sales amount, not the sum of the sales amount for each data point of a visual.
• It doesn't currently work with measures.
• You can type any number into a numeric slicer, even if it is outside the range of values in the underlying column. This option lets you set up filters if you know the data may change in future.

https://docs.microsoft.com/en-us/power-bi/desktop-slicer-numeric-range#limitations-and-considerations

Regards,

Lin

[Anonymous]

Thanks Lin,

I guess the work around in this case would be to train users to try to use the Slider feature instead of the calendar And maybe add a date range on the report for clarity.

[amitchandak]

@Anonymous , This is what I was saying. Not sure We are on the same page.

File attached after Signature

@Anonymous 

[Anonymous]

It is possible. Each visual, even a slicer, can have a filter put on it. The values in the slicer can be filtered by a measure. Just create a measure that for each date will return 1 if the date is greater or equal to the earliest date available in the data set and before or on the last date in the data set. Then put a filter on the visual that says to show only those dates where the measure = 1.

Best
D