Dynamic end date slicer based on start date slicer selection

[Anonymous]

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 start_date slicer like below but selectedvalue is returning null

end_date_table = 
var selected_start_date = SELECTEDVALUE(start_date_table[Start Date])
return 
ADDCOLUMNS(
    CALENDAR(format(selected_start_date, "YYYYMMDD"), DATE(2021,8,30)),
    "End Date", FORMAT([Date], "YYYY-MM-DD")
)

Error: Cannot convert value '' of type Text to type Date.

I tried to follow this solution by creating parameters still could't achieve the desired solution. 

https://community.powerbi.com/t5/Desktop/Passing-Parameters-in-measures/m-p/208276

pbix file link - removed

[amitchandak]

@Anonymous , you can not create a new  table based on slicer value

[Anonymous]

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 · Accepted solution]

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]

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]

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]

@Anonymous  

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