Custom Range Date Slicer in Power BI with dynamic defaults and more...

Setting default value dynamically for slicers was one of most requested feature in ideas.powerbi.com for many long time. We already have relative date functionality available in default slicer visual but it brings another challenge: allowing end users to both select relative ranges and custom interval at the same time. In many times we end up with creating complex DAX measures to switch between different slicers, date tables and more to fulfill these requests.

In this article I will focus to introduce new possibilities to improve date filtering experience by utilizing and combining built in features of slicers, buttons and bookmarks with minimum DAX requirement and without any custom visual.

At the and of the article we are aiming to reach below experience allowing setting dynamic date ranges as default value, setting pre-defined ranges quickly and allowing users to switch custom range selection easily.

Step 1: Create "SpecialDates" Table

The first step in our solution is defining a calculated date table using below formula. It seems complicated but just copy/paste and believe me it worths to get rid of many DAX measures in future :)

The only thing you need to change is table name in second row: DateTable. Then you can adjust calculated table DAX formula based on your custom range needs: eg. Previous Month or Last 2 Weekend.

SpecialDates = 
VAR _datetable = DateTable
VAR _today = TODAY()
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _thismonthstart = DATE(_year,_month,1)
VAR _thisyearstart = DATE(_year,1,1)
VAR _lastmonthstart = EDATE(_thismonthstart,-1)
VAR _lastmonthend = _thismonthstart-1  
VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)


RETURN UNION(
    ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1),
    ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),"Period","Last 7 Days","Order",3),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thismonthstart),"Period","This Month","Order",4),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",7),
    ADDCOLUMNS(_datetable,"Period","Custom...","Order",8)
    )

Step 2: Create relationship between SpecialDates table & your date table

In next step, we will open modeling view and create a relationship between your existing date table and our SpecialDates table as below. Filter direction should set "both" here.

Step 3: Put a slicer to canvas and apply some formatting

Now, our data and model is ready and we will test our logic in a report page. Drag period column in the table to a slicer visual and set single select in configuration. You can also put any other measure to another visual in canvas to test date filtering functionality!

Basic functionality is now working but we need to enhance this experience a bit more.

Step 4: Add a button on top of slicer

Now, we will add a blank button to our canvas and set its text label value with our one and only measure following formula:

ButtonLabel = 
    VAR _selectedperiod = SELECTEDVALUE(SpecialDates[Period])
    VAR _start = MIN(SpecialDates[Date])
    VAR _end = MAX(SpecialDates[Date])
    RETURN SWITCH(true,
        _selectedperiod = "Custom...",FORMAT(_start,"d mmm")&" - "&FORMAT(_end,"d mmm")&" ?", 
        _selectedperiod = "Today", FORMAT(_start,"d mmm yyyy") ,
        _selectedperiod = "Yesterday", FORMAT(_start,"d mmm yyyy") ,
        FORMAT(_start,"d mmm")&" - "&FORMAT(_end,"d mmm") 
    )

You can customize this formula based on your custom formatting needs. And then you can set Text value of blank button as this new measure as below.

As a very minor addition, you should check Maintain layer order checkbox in General section of Button Formatting options and Slicer Formatting options. Otherwise button can disappear when users clicks slicer border.

Now you can test your slicer and see button text is changing according to range selection. When you save your report with any slicer value, it will be automatically show dynamic date range to end users after each dataset refresh. (At least daily refresh is required for this table logic but you can customize it based on your needs)

Step 5: Add Support for Custom Date Range Selection

In this step, we will add another slicer to canvas for custom date selection which will be visible when end users click on our button.

After adding date slicer using Date column in our table, add a simple reset button left for allowing users to hide custom slicer. After having all visuals in our design, we need to setup 2 bookmarks to hide and show custom selection option.

Firstly, select value "Custom..." from top slicer, open selection pane and select 4 visuals including 2 buttons and 2 slicers. Then Add Bookmark and rename it as CustomRange.

Then select value "Last 7 Days" or any other default value from top slicer and hide reset button and custom date slicer using selection pane. Then select all four visuals again from selection pane and Add Bookmark with name StandardRange.

Last thing we need to do now is testing button functionality by clicking on date button and reset buttons. If everything seems fine, then move custom date slicer and reset button on top of main slicer and test it again! You may adjust font sizes, headers and border colors to finetune final design.

Final output should give experience like below:

That’s all for my solution for custom range date slicer with dynamic default value. You can extend same approach with measures instead of using calculated tables or add additional capabilities such as Last X Days support. If you have any feedback or improvement ideas on top of this, feel free to share your comments below. I can't guarantee to answer your questions regarding this solution but you may use attached PBIX to dig in to sample and understand the logic.

Hope to see you in another post!