topic Re: Dynamic end date slicer based on start date slicer selection in DAX Commands and Tips

Dynamic end date slicer based on start date slicer selection

Anonymous — Wed, 24 Nov 2021 20:25:30 GMT

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

Re: Dynamic end date slicer based on start date slicer selection

amitchandak — Thu, 18 Nov 2021 10:03:18 GMT

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

Re: Dynamic end date slicer based on start date slicer selection

Anonymous — Thu, 18 Nov 2021 21:20:56 GMT

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.

Re: Dynamic end date slicer based on start date slicer selection

Anonymous — Tue, 23 Nov 2021 09:34:51 GMT

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

Re: Dynamic end date slicer based on start date slicer selection

Anonymous — Wed, 24 Nov 2021 23:52:35 GMT

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

Re: Dynamic end date slicer based on start date slicer selection

Anonymous — Thu, 25 Nov 2021 01:21:04 GMT

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

Re: Dynamic end date slicer based on start date slicer selection

Anonymous — Thu, 25 Nov 2021 19:38:17 GMT

Anonymous

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