Load separate date columns in start and end date picker slicer based on another drop down slicer

[surajshashidhar]

Hello All,

Basically i have data tables which are 
1. date_dim ===> (Date, WeekStartDate, WeekEndDate, MonthStartDate, MonthEndDate, ...) ===> single select dropdown slicer
2. Granularity ===> ('daily', 'weekly', 'monthly', ...) ===> Date Picker with "between" filter

Problem: Currently the Date Range slicer and picker gets activated for all dates in date_dim as we are using date_dim[Date] for filtering. But we need to change the start and end dates loaded into calendar date picket based on selected drop down from Granularity.

For example: If user selects 'monthly' , then we need to Make only month start date selectable from calendar date picker for start date and month end date for end date. similarly for week granularity as well.

We tried creating two measures that switches between daily date, weekly and monthly date based on granularity but it does not seem to change the dates loaded onto calendar date picker.

Are there any ways of solving this problem? Is this possible in power bi to load only specific dates into calendar date picker?

[Anonymous · Accepted solution]

Hi, @surajshashidhar 

This feature isn't currently available in Power BI, but if you want to show the beginning and end of the month directly, you can use the drop-down list setting. I create sample tables:

Then create a new measure:

IsSelectableDate = 
VAR Logic = SWITCH(
    SELECTEDVALUE(Granularity[Granularity]),
    "daily", TRUE(),
    "weekly", SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Date'[WeekStartDate]) || SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Date'[WeekEndDate]),
    "monthly", SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Date'[MonthStartDate]) || SELECTEDVALUE('Date'[Date]) = SELECTEDVALUE('Date'[MonthEndDate]),
    FALSE()
)
RETURN
IF(Logic = FALSE(), BLANK(), "True")

Create a Granularity slicer, then a date slicer, and put the created measure into the filter, setting the filter to True. This way, when the Granularity slicer is clicked, the date slicer will show the beginning of the month and the end of the month, or Monday and weekend.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

Hi @surajshashidhar ,

As far as I know, this feature is not there in PBI. even if there are only two dates in a table, the date picker will work for all the dates in between the two dates. its a standard visual. The dropdown will work for the specific dates.