The Ultimate Date Slicer for Power BI

Recently I came across an insanely entertaining fresh video from Parker Stevens about Custom Date Period Selections in Power BI (yes, I know, not very fresh – I rarely enter the Internet these days, just look at the date of my last article :)). Everything in this solution is good, except for one thing – if the user has chosen a custom period, and then switched to one of the preset ones, the filter behaves somewhat illogical from the user’s point of view.

Of course, you can teach the user to reset the filter before switching, or use the eraser on the slicer, but … I’m not going to tell you how difficult it is sometimes to educate users. And the common sense in this situation is not on the side of the report developer.

The reason for this behavior of the slicer is quite understandable – the filter is applied to two different fields in different tables, and there is no way to “program” reset the filter in one table when choosing a value in another. Power BI just not able to set a trigger for a filter change event. Or… able?

Bookmarks: Rescue Rangers

Somehow it happened that in my blog I have never mentioned bookmarks. Nevertheless, in my reports, their number sometimes exceeds a hundred (or even several hundred). And if you ask me what function has become a game-changer for Power BI, I will answer without hesitation – buttons with bookmarks. After all, the end user, in general, does not really care what a cool composite model with three levels of aggregation based on Azure Synapse or Premium Gen2 is under the hood. If the interface of this report is the same user-friendly as the DOS interface, it will go to the dump.

So, using bookmarks can quite help us solve this problem with the date slicer. You just need to create a set of bookmarks that activate certain periods on a separate period slicer, and at the same time reset the filter directly from the date column. So, what are we waiting for?

Step 1. Following Parker

No, we will not blindly repeat after the master. In addition to the usual date table (inside which, for various reasons, you can put future dates), we will create another, technical table, with dates up to the desired anchor date. After all, today is not always the reporting day, or the last day in the fact table, right?
Here’s my code for this cute table:

ReportDates =
CALENDAR (
    MIN ( SellIn[Date] ),
    MAX ( SellIn[Date] )-- any anchor date you want
)

And after that, we create a table with our desired preset periods. For example, you can use this slightly edited code from Parker’s video:

Date Periods =
UNION (
    ADDCOLUMNS (DATESMTD ( 'ReportDates'[Date] ), "Type", "MTD", "Sort", 1),
    ADDCOLUMNS (DATESQTD ( 'ReportDates'[Date] ), "Type", "QTD", "Sort", 2),
    ADDCOLUMNS (DATESYTD ( 'ReportDates'[Date] ), "Type", "YTD", "Sort", 3),
    ADDCOLUMNS (
        PREVIOUSMONTH (DATESMTD ( 'ReportDates'[Date] )),
        "Type", "Last Month",
        "Sort", 4
    ),
    ADDCOLUMNS (
        PREVIOUSQUARTER (DATESQTD ( 'ReportDates'[Date] )),
        "Type", "Last Qtr",
        "Sort", 5
    ),
    ADDCOLUMNS (
        PREVIOUSYEAR (DATESYTD ( 'ReportDates'[Date] )),
        "Type", "Last Year",
        "Sort", 6
    ),
    ADDCOLUMNS (
        CALENDAR (MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] )),
        "Type", "All Time",
        "Sort", 7
    ),
    ADDCOLUMNS (
        CALENDAR (MIN ( 'ReportDates'[Date] ), MAX ( 'ReportDates'[Date] )),
        "Type", "Custom",
        "Sort", 8
    )
)

Link this table to our date table using a bidirectional relationship:

Let’s add two slicers (with dates and preset periods) and the trivial chart with a date axis. Also, insert a semi-transparent shape as in the screenshot – you will see later why. Turn on the display of selection and bookmarks panes in the ribbon:

We are now ready to build our UX

Step 2. Bookmarks for periods

Time to create a bookmark for each period from the period slicer.
To do that:

• select one of the periods (MTD, for example)

• click the “Clear selections” icon on the date slicer (this is important – otherwise the tab will “hardcode” the dates displayed right now, and when new dates are added to the model, everything will break)

• hide the period slicer

• select both slicers and a semi-transparent shape on the panel using Ctrl

• create a new bookmark, in which we leave only the “Data”, “Display” and “Selected visuals” options

• turn on the visibility for the period slicer again
• select the next period and repeat all steps up to the “Custom” option

For the “Custom” item, the configuration of the bookmark changes slightly – in it, our semi-transparent shape should also be invisible. You already understood that with its help we will “protect” our date slicer from any additional changes by the user, unless the “Custom” item is selected. And it is transparent so that the user can see specific dates in addition to the abbreviation (sometimes it is useful to see what exactly is hidden under the MTD, agree?)

In general, our bookmarks should work somehow like this:

Step 3. UI time

Now the easiest part – for each bookmark we need to draw a button, place them somehow beautifully, and create two more buttons + bookmarks that will hide / show our self-made menu.

In my case, the order is something like this:

• create 9 buttons
• create some kind of decoration for them and the main slicer

• group the created elements into the “Menu” group
• draw two small buttons, one above the other

• create a bookmark to enable the menu: select both small buttons and the “Menu” group, make one of the small buttons invisible, create a bookmark, leave only the options “Display” and “Selected visuals”

• create a bookmark to turn off the menu: select the same elements, show the previously hidden small button, hide the rest, create a bookmark with the same options

• apply an “Action” to each button – the corresponding bookmark

• for each element, enable the “Maintain Layer Order” option, or use the trick from my previous article
• test

As a result, if everything is done correctly, we get the desired result.
The user gets the opportunity to choose a predefined period or choose the dates on his own. All this happens within if not ideal, but quite tolerable and understandable interface, in which everything works without surprises and pitfalls.

Of course, the source file can be downloaded from here

Nota bene

An attentive developer will notice that after all these manipulations, time-intelligence functions stop working. For example, CALCULATE ([_ QTY], SAMEPERIODLASTYEAR (‘Date’ [Date])) in my case will stubbornly return an empty value.

As you probably already guessed, the reason is our bidirectional relationship between the date table and the period table.

Solving this is quite simple, although it must be constantly kept in mind. You just need to write in DAX to ignore filters coming from the period table. For example, using REMOVEFILTERS or ALL:

_Qty PY =
CALCULATE (
    [_QTY],
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    REMOVEFILTERS ( 'Date Periods' )
)

If someone finds a way how to get around the extra line in the formulas / bidirectional relationship – please share in the comments, I’ll show you a beer. Seriously 🙂

What’s next

Further actions depend only on your imagination and the wishes of the client.

If you want more preset periods – please, even 50, if only you can fit them on the screen

You don’t like the limitation with a transparent shape? – remove it and allow the user to adjust the dates within the selected period (however, beware of that illogical UX, which I talked about at the beginning of the article)

Do you need to use a different anchor date? – please change the technical date table script to fit your request.

If you want to reduce the number of clicks – overwrite the bookmarks so that the menu is immediately hidden when you press the button with a preset period.
And so on.

And I didn’t mention the buttons themselves can be improved in terms of design and response to user actions.

I think the users of your report will appreciate this flexibility. Do you agree? 😉