Hello - Ok, I'm struggling with the calendar slicer visual. The requirements are that the slicer be a slider with a popup calendar for begin and end dates, and that the begin dates be limited to Saturdays, and end dates limited to Fridays. I posted another entry asking whether it's possible to display the calendar with all dates greyed out except Saturdays (for begin dates) and Fridays (for end dates). This does not seem to be possible, either directly or in a customer visual or app. The requirement for a slider and popup date calendar is to keep the report consistent with all other Power BI reports.
The reason I need to limit the dates is that payroll weeks are Saturday to Friday, and I need to display full weeks of data. If a user selects Wednesday as a begin date, then a partial week will show, and the comparisons on the visuals will not be apples to apples. I'm trying to make the calendar dummy proof, so that while you have the flexibility to specify a date range, the output will be full payroll weeks, if that makes sense. I considered using a popup message if you select something other than Saturday for begin date or Friday for end date, but this is not elegant at all.
Is it possible to override the dates entered in the slicer? If a user selects Wednesday, March 10, 2021 as the begin date, the begin date will be overwritten to use the Saturday before - March 6, 2021. If a user selects Thursday, April 1 as the end date, the end date will be overwritten to use the following Friday - April 2nd. Is it possible to do this? Thanks!!!!!!!
That makes sense. A couple of questions:
How do I return no data when the user selects incorrect dates?
How do I display a message when the input is incorrect. Is that with a text box? I tried that, but when the entry is correct, the text box appears also. I couldn't figure out how to conditionally show the text box. I created filters and the message (based on the filters), but couldn't get the box to not appear when the start and end dates were correct (i.e., user selected Saturday and Friday dates, respectively).
Hey @ConnieMaldonado ,
you can in every measure just give back values when the dates are set correct:
MyMeasure = VAR vStartDate = MIN( myDateTable[Date] ) VAR vStartDateDay = WEEKDAY( vStartDate, 2 ) VAR sEndDate = MAX( myDateTable[Date] ) VAR vEndDateDay = WEEKDAY( sEndDate, 2 ) VAR vCalculation = SUM( myTable[Sales Amount] ) // Or whatever you really want to calculate RETURN IF( vStartDateDay <> 6 || vEndDateDay <> 5, BLANK(), vCalculation )
Then you add a text box in the center and add a measure as title:
MyWarning = VAR vStartDate = MIN( myDateTable[Date] ) VAR vStartDateDay = WEEKDAY( vStartDate, 2 ) VAR sEndDate = MAX( myDateTable[Date] ) VAR vEndDateDay = WEEKDAY( sEndDate, 2 ) VAR vCalculation = "Please choose a Saturday as start day and a Friday as end day" RETURN IF( vStartDateDay <> 6 || vEndDateDay <> 5, vCalculation , BLANK() )
This should solve your problem.
This is a great solution; however, the text box still appears (although it's blank) when the correct entries are made in the date slicer. Is there a way to have it disappear?
Hey @ConnieMaldonado ,
no sadly you cannot make it disappear.
You could also give back the message in a measure. But that's more or less all the possibilities you have.
Hello @ConnieMaldonado ,
to your question, yes that is possible.
From my experience it might be confusing for the user when he selects a date and he gets result for another date range.
So I personally would only return values when the user is choosing a correct date range. When he is choosing wrong dates I would return no data, but only a message, that the start day has to be a Saturday and the end date has to be a Friday. Then the user is in charge to use the report properly and he is not confused that the date range is changing.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!