Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a date slicer to allow the user to select a beginning date and ending date. I need to limit the beginning dates to Saturdays only, and the ending dates to Friday only. I'd like the calendar to appear with all other dates greyed out, only allowing Saturdays for start date, and Fridays for end date. I couldn't figure out a way to do this. I've attached a PBIX. Any help is appreciated. Thanks!
Link to PBIX:
https://www.dropbox.com/s/drx8av8yhfhhymc/Calendar%20Blackout%20Dates.pbix?dl=0
Solved! Go to Solution.
You cannot control a slicer the way you describe. You could add a filter on the slicer using day name or day of week from the calendar and limit it to Fridays and Saturdays, but that would not stop someone selecting start on Fri and end on Sat. You could load 2 disconnected tables of dates, (one for start and one for end) that only contain Sat and Fri dates respectively. You would then need to modify your measures to respect these disconnected tables for every measure (or possibly use a calculation group, I guess).
CALCULATE([measure],FILTER(calendar,calendar[date] >=selectedvalue(start[date]) && calendar[date] <=selectedvalue(End[date])))
where Start and End are your 2 new tables.
You could write a measure and use it as a filter in the end slicer. But I would be careful doing this for both slicers as they may trip up on each other. You could also write a measure that returns a warning message if the to date is after the start date
You cannot control a slicer the way you describe. You could add a filter on the slicer using day name or day of week from the calendar and limit it to Fridays and Saturdays, but that would not stop someone selecting start on Fri and end on Sat. You could load 2 disconnected tables of dates, (one for start and one for end) that only contain Sat and Fri dates respectively. You would then need to modify your measures to respect these disconnected tables for every measure (or possibly use a calculation group, I guess).
CALCULATE([measure],FILTER(calendar,calendar[date] >=selectedvalue(start[date]) && calendar[date] <=selectedvalue(End[date])))
where Start and End are your 2 new tables.
Fair enough. Is there any way to force the selected values to be the Saturday before the date selected (for the begin date) and the Friday after the date selected (for the end date).
So if you selected Wed, Mar 17, 2021 for the begin date, the selected value would actually use Sat, Mar 13, 2021. If you selected Tue, Mar 23, 2021 as the end date, the selected value would actually be Fri, Mar 26. The reason is that the payroll weeks run from Sat to Fri, and I need to include full weeks in the slicer so that the results appear for full weeks rather than partial. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |