How to auto-reset “Custom” date range when switching back to predefined ranges

[KL124]

I’m building a dynamic date selection in Power BI with a standard Calendar table and a helper table for predefined ranges + a “Custom” option. Users can pick a predefined period (e.g., WTD, MTD, last month) or switch to “Custom” and select arbitrary dates.

Problem
If a user selects a Custom range and then moves back to any predefined range, the previously chosen custom dates keep filtering the model. I need a DAX/table-only approach so that, whenever a predefined range is selected, the “Custom” selection is effectively ignored (reset to the full min/max of Calendar[Date]).

Constraints

• Prefer DAX (tables/measures) only—no external tools and no changing of data measures I use to calculate indicators as there are too numerouse. 

• Slicers may appear on multiple report pages.
  
  

  Current Setup

  Flag in Calendar to detect Custom type:

   Select custom type = IF ( SELECTEDVALUE ( 'Dynamic Date selection'[Type] ) = "Custom", 1, 0 )

  Helper table (predefined + custom):

   Dynamic Date selection = UNION ( ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurWeekOffset] = -0 ), "Date", 'Calendar'[Date] ), "Type", "Week to date", "Order", 1 ), ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurWeekOffset] = -1 ), "Date", 'Calendar'[Date] ), "Type", "Last week", "Order", 2 ), ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurWeekOffset] = -2 ), "Date", 'Calendar'[Date] ), "Type", "The week before last", "Order", 3 ), ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurMonthOffset] = 0 ), "Date", 'Calendar'[Date] ), "Type", "Month to Date", "Order", 4 ), ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurMonthOffset] = -1 ), "Date", 'Calendar'[Date] ), "Type", FORMAT ( MAXX ( FILTER ( 'Calendar', 'Calendar'[CurMonthOffset] = -1 ), 'Calendar'[Date] ), "MMMM YYYY" ), "Order", 5 ), ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( 'Calendar', 'Calendar'[CurMonthOffset] = -2 ), "Date", 'Calendar'[Date] ), "Type", FORMAT ( MAXX ( FILTER ( 'Calendar', 'Calendar'[CurMonthOffset] = -2 ), 'Calendar'[Date] ), "MMMM YYYY" ), "Order", 6 ), ADDCOLUMNS ( CALENDAR ( MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ), "Type", "Custom", "Order", 7 ) )

  What I want

  • When a predefined Type is selected, any prior Custom date range should be disregarded—effectively reverting Custom to MIN(Calendar[Date]) … MAX(Calendar[Date]).Right now the custom filter keeps it values when the user selects a predefined date range. This setup is on multible pages. 

[v-saisrao-msft]

Hi @KL124,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @KL124,

Have you had a chance to review the solution we shared by @pbiuseruk @WishAskedSooner ? If the issue persists, feel free to reply so we can help further.

Thank you.

[pbiuseruk]

Hello,

I think a bookmarks solution like @WishAskedSooner is definitely the cleanest way of handling this but an alternative approach which is alot easier and quicker to implement, would be to make 2 fields for slicing instead and just having a button to clear filters.

e.g. Someone puts in a custom date into the date slicer. Then they want to use a predefined range from the other options. They click on the button to reset the range and then go to the second slicer to select a predefined date range.

You'd have to maybe inform your users of how it works and would probably be useful to add help tooltips but this would be the easiest way.

[WishAskedSooner · Accepted solution]

@KL124,

Hello,

The easiest way to handle this is via Bookmarks. I will give a high level solution:

1. Create the various time slicers that you will need e.g. Period (with WTD, MTD, etc) and Custom.

2. Now, create a disconnected table with the entries that represent which Bookmark they have selected e.g. Period and Custom.

3. Put #2 into its own slicer.

4. Now, create Bookmarks that save the state of the slicer in #3 making sure to select Period when that Bookmark is selected and Custom when the other Bookmark is selected. Be sure to select that the Bookmark saves the Data and usually best only Selected Visuals. You should also hide the Slicer in #3.

5. Finally, you will need to modify your measures to include a VAR _Sel = SELECTEDVALUE( _disconnectedStateTable[Selection] ) and use that in a IF or SWITCH.

Warning! Working with Bookmarks is tricky which is why I avoid them unless absolutely necessary. You need to be very careful that you have everything correctly configured, filtered, selected, hidden, visible, etc. before saving or updating your Bookmark. Constant back and forth testing is advisable.

Good luck!