Forum Discussion
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 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
Hi Anonymous ,
Please review the solution in the following thread and check whether that is what you want.
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
6 Replies
- amitchandak
Super User
Anonymous , you can not create a new table based on slicer value
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.
Hi Anonymous ,
Please review the solution in the following thread and check whether that is what you want.
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
