Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Drop down box to filter two dates

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 ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    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's avatar
Samarth_18
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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