Drop down box to filter two dates

[Anonymous]

Hi everyone,

I am setting up a report page with multiple charts. I want to have on the left of the page two drop down boxes with start date to filter and then end date so people can choose which dates they want to filter on. So they can choose jan-2015 to feb 2016 or what ever time period  they choose that is in my data. How do i set this up? between slicer does not work because it is the bar that slides along not a drop down box and relative slicer does not work because it is not flexible with the date window you can select. I cannot find any tutorial online which I thought was strange.

Thanks for any help

[Anonymous]

Thanks Samarth

When I do the steps above my second drop down box only lets me select the date in the first drop down box. The second drop down box filters on the first drop down box. Also the date ordering is alphabetical not cronological. How do I change that?

Thanks

[Samarth_18 · Accepted solution]

For filtering second drop down based on first you can create a measure as below and use it as visual level filter on visual.

end_date_filter =
IF ( MAX ( _end_date[Date] ) >= SELECTEDVALUE ( _start_date[Date] ), 1, 0 )

And sorting your dates make sure you have marked your date column type  as date from ribbon.

Once you set it as date and click elipsis(three dot) of slicer you will get sort option like below. so you can use it for sorting according to your need.

[Samarth_18]

Hi @Anonymous 

You need to create two custom table one for start date and one for end date.

_start_date = CALENDAR(MIN(table[Date]),MAX(table[Date]))

_end_date = CALENDAR(MIN(table[Date]),MAX(table[Date]))

Note:- Pass your that table in above code from which you want to create a date range based on your available data.

Add these two table as dropdown slicer

Now create a measure to filter your visual based on your selection as below

_filter_data = if(MAX(table[Date])>=SELECTEDVALUE(_start_date[Date]) && MAX(table[Date])<=SELECTEDVALUE(_end_date[Date]),1,0)

Create this measure for each of your visual which you want to filter based on selection and add it as visual level filter. Just make sure you are passing date that is used in specific visual.

Thanks,

Samarth

[Anonymous]

Thanks Samarth, I get an error saying DAX does not allow dates to be compared to text. Also how do I order my dates cronologically in the slicer drop down box and not alphabetically?

Thanks