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

Overwrite Dates Entered in Calendar Slicer

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!!!!!!!

 

Slicer.PNG

5 REPLIES 5
ConnieMaldonado
Resolver II
Resolver II

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.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
selimovd
Community Champion
Community Champion

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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