Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic end date slicer based on start date slicer selection

Hi, I'm trying to have two slicers as start_date and end_date. I want to custom end_date list based on start_date slicer selection.  I tried to create a end_date table by passing selectedvalue from...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please review the solution in the following thread and check whether that is what you want.

    Dependent date slicers

    1. Create two calendar tables: Calendar A and Calendar B.

    2. On page 1, create a date slicer with 'Calendar A'[Start of Month] as field. Copy the date slicer to page 2 and sync these two slicers as below. Hide the slicer on page 2.

    3. Create measures:

    Selected Date = SELECTEDVALUE('Calendar A'[Start Of Month])
    Measure = IF(MAX('Calendar B'[Start Of Month])<=[Selected Date],1,0)

    4. On page 2, create a new date slicer with 'Calendar B'[Start of Month] as field. Add Measure into this slicer's visual filter and set value is 1. 

    5. The values may not be selected automatically, so I show "Select all" option in the slicer for user to select all the dates before the date selected on page 1. 

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks! amitchandak  Is there a workaround? or how do I make sure my end_date values are always greater than start_date values. I can't have a single slicer for a date. I need to have two slicers as I have to pass these values to the M query as a parameter. 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

Please review the solution in the following thread and check whether that is what you want.

Dependent date slicers

1. Create two calendar tables: Calendar A and Calendar B.

2. On page 1, create a date slicer with 'Calendar A'[Start of Month] as field. Copy the date slicer to page 2 and sync these two slicers as below. Hide the slicer on page 2.

3. Create measures:

Selected Date = SELECTEDVALUE('Calendar A'[Start Of Month])
Measure = IF(MAX('Calendar B'[Start Of Month])<=[Selected Date],1,0)

4. On page 2, create a new date slicer with 'Calendar B'[Start of Month] as field. Add Measure into this slicer's visual filter and set value is 1. 

5. The values may not be selected automatically, so I show "Select all" option in the slicer for user to select all the dates before the date selected on page 1. 

Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much Anonymous . The above approach works. But got below error when I tried to bind a parameter to start date and end date

     

     

    Solution to my question.

     

     

    start_date_table = 
ADDCOLUMNS(
    CALENDAR(DATE(2021,8,1), DATE(2021,8,30)),
    "Start Date", FORMAT([Date], "YYYY-MM-DD")
)
    end_date_table = 
ADDCOLUMNS(
    CALENDAR(DATE(2021,8,1), DATE(2021,8,30)),
    "End Date", FORMAT([Date], "YYYY-MM-DD")
)
    Measure = IF(MAX(end_date_table[End Date]) >= [selected_start_date], 1, 0)
    selected_start_date = SELECTEDVALUE(start_date_table[Start Date])

     

     

    On page1, create a slicer with Start Date

    Copy paste the slicer to page two

    You get a pop up asking for sync - click sync

     

    then on page two create slicer with End Date

    drag on drop measure field into the End Date slicer and set measure is 1. Apply filter.

     

    Hide the page 1

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ，

      Thanks for sharing your solution here, it will help the others in the community find the solution easily if they face the same problem with you. Much appreciated!

      Best Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous  

        I'm getting the below error when I tried to bind a parameter to start date and end date. Can you please help?