cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnieMaldonado
Resolver II
Resolver II

Date Slicer Blackout Dates

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

 

1 ACCEPTED 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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors