Date Range slicer start date and end date to custom dates

[giridhara]

I have a table of sales data, The sales data contain the store location, date, and sales total.

I use a slicer with a date column and slicer option style as between to get a start and end date option in visualization.

When I freshly open the report the filter start date and end date are set to values that are not available in my table, How to restrict them?

I am trying to figure out if I can set the start date to min(table[date]) and the end date to max(table[date]) to the filter, so the date picker filter will start with the dates I have in my table.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with this Table formula

Calendar = calendar(min(table[date]),max(table[date]))

Create a relationship (Many to One and Single) from the Date column of Table to the Date column of the Calendar Table.  Create your slicer from the Date column of the Calendar Table.

Hope this helps.

[Priya1969 · Accepted solution]

Store Name	Location	Date	Sales
Walmart	IL	12/10/2022	1000
Walmart	NY	12/10/2022	1200
Costco	IL	12/11/2022	700
Walmart	NY	12/11/2022	500
Costco	NY	12/12/2022	2000

1.Create a two slicer one with store name and other one with date

2. Create a Matrix visual with store name, date, and sales value

3. Both the slicer format pane, select slicer setting, off the multi select, and on the "Select all" option

4. Choose slicer option dropdown instead of between. Now you can see the desired output. 

[giridhara]

The list is working well, but when I use the between in slicer settings and select one start date and one end date, and I change the store name, the dates are not defaulting to the first date of the dates table as start and the last date of the date table as end date.

E.g.: 

As per your data, I selected Costco as the option, and Even if Costco doesn't have data on the 10th, the date picker is still showing it. Can we make this date picker dynamic which can default to the start date as min date and the end date as the max date for the data reflecting after selecting the filters?

[Priya1969]

The between option is not working out. I am trying in multiple ways... still struggling to figuring out.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with this Table formula

Calendar = calendar(min(table[date]),max(table[date]))

Create a relationship (Many to One and Single) from the Date column of Table to the Date column of the Calendar Table.  Create your slicer from the Date column of the Calendar Table.

Hope this helps.

[giridhara]

Will these values update based on the other filters?

I have a list drop-down filter that gives stores.

In the filter, If I select the Store name as SR then the date filter should automatically show the start date of 12/10/2022 and the end date of 12/11/2022. 

If I select DR then the start date should be 12/10/2022 and the end date is 12/12/2022. Are the filters can update the values?

Store name	Location	Date	Sales
SR	PX	12/10/22	1000
DR	PX	12/10/22	1000
SR	MO	12/10/22	500
SR	MO	12/11/22	500
DR	MO	12/12/22	1000

[Priya1969]

Store Name	Location	Date	Sales
Walmart	IL	12/10/2022	1000
Walmart	NY	12/10/2022	1200
Costco	IL	12/11/2022	700
Walmart	NY	12/11/2022	500
Costco	NY	12/12/2022	2000

1. Create a two slicer one with store name and other one with date

2. Create a Matrix visual with store name, date, and sales value

3. Both the slicer format pane, select slicer setting-selection, off the multi select, and on the "Show Select all" option

4. Choose slicer option dropdown instead of between. Now you can see the desired output. 

[Ashish_Mathur]

Hi,

Create a Date slicer from the Calendar table and select a range of dates there.  Create a slicer of Stores and select a certain store.  To your Matrix visual, drag Date from the Calendar table and write this measure

Measure = sum(Data[Sales])

Hope this helps.