Reset Date Slicer when Period Table are in use

DAX DAX Calculations
1

Hi,

I am trying to combine the smart functionalities of having a predefined Period Table, as @Melissa has made with a Custom Date Range selection from BI Elite

Link to Melissa’s Period Table: (dynamic date range slicer query m code

Link To BI Elites video: Custom Date Period Selections in Power BI

I added a ‘Custom date’ option to the period table (giving me all dates for the latest 3 years):

{"Custom date", Date.From(Date.AddYears(Date.StartOfYear(TodaysDate), -3)), TodaysDate, 17}

I then added a ‘Date slicer’ to be used when “Custom date” is selected:

image

As shown in BI Elites video, I filtered the ‘Date Slicer’ using this DAX code, and then set it =1 on the slicer:

Date Slicer Filter = 
IF(
    SELECTEDVALUE('Period table'[Period]) = "Custom date",
    1,
    0
)

image

The problem occurs If you choose your custom date range to be anything from the minimum, and then go and select another period from the Periods table, then the dates inside the Date Slicer will stay unchanged.

The steps i made:

  1. Select custom date
    image

  2. change date in date slicer:
    image

  3. Select another Period:
    image

As you can see, It filters the data correctly, but it keeps the date I selected from the custom date. It is a bit confusing for the users that the ‘Date slicer’ is still showing the previously selected date range.

Is there is a way for the “Date Slicer” to reset when a period is selected?

See example here: custom date selection and date period.pbix (872.6 KB)

2

Hi @JNordentoft,

Please provide a sample PBIX
Thanks!

1 Like
3

Hi Melissa, I have attached a sample pbix now

1 Like
4

Hi @JNordentoft,

:thinking: So we can’t hide / show slicers based on conditions, control slicer visibility with conditional formatting or enable a default slicer state with bookmarks…

How do you feel about Buttons? If instead of a Period slicer you’d create Period Buttons you could use Bookmarks to control the Date slicer

5

@JNordentoft,

Per @Melissa’s suggestion, there’s an approach I use that I refer to as a pseudo-slicer, which is a series of buttons tied to bookmarks or (as in the example below) page navigation actions. I use this approach as a workaround to the limitations of conventional slicers that you and she discuss above. Even this simple example demonstrates the range of flexibility you have with the enhancements Microsoft has made to the navigational features:

I hope this is helpful.

  • Brian
6

Hi @Melissa and @BrianJ,

I ended up using buttons as you suggest. To keep it simple I just duplicated the page and switched the date selection, so the first page is showing “custom dates” and the second the “period filters”

image

Thanks a lot for the help both of you!

2 Likes
7

Hi @JNordentoft, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @JNordentoft , we’ve noticed that no response has been received from you since the 18th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @JNordentoft, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like
10

Hi,

Sorry for the late respons, havn’t been active for a while.

I used the buttons option, so that the default state is showing the last 7 days (using Melissas period solution), and then use a button to switch to another report page for custom date selection :slight_smile:

1 Like