To have a Date slicer default to the last ten years but the user can then broaden their date range

[D_PBI]

Hi
I have a Slicer visual that contains the dimension table's dimDate[Date] field. The date values present for this field are from 12/03/1997 to 30/06/2025. These are also the earlier and latest dates that can be found in the dimDate table.
I would like to so when the report is loaded the dates shown in the Slicer visual is from today's date going back to the 1st January but 10 years back. So if today is 02/07/2025 then the default Slicer visual date range should be 01/01/2025 to 02/07/2025 and the downstream visuals should present data based on this date range.

However, if the user wishes to filter on a greater date range (remember the full date range is between 12/03/1997 and 30/06/2025) then the user can make the adjustment in the Slicer visual and then the downstream visuals will reflect this amended date range.

The date dimension table is dimDate[Date] and joins to the downstream fact table case(Sale Date] field, in case this is relevant for examples.
Is this possible and how could I do it?  Thanks.

[D_PBI]

@v-mdharahman thanks for your reply.
I'm not fully understanding the instructions and it isn't working.

I have a report. It's a single page report but with 5 bookmarks (so it's already using bookmarks).
Bookmark 1 uses the first date dimension table.
Bookmark 2 uses the second date dimension table.
Bookmarks 3, 4 and 5 uses the third date dimension table.

This report is then embedded to a webpage. For the setup of this report's configuration, I have the 'Data' setting on each Bookmark unchecked - it has to remain unchecked.

To continue with what I've tried.
I've created the Date Flag, with the logic specified, as a column in Bookmark one's date dimension table. I've dragged that column into the 'Filters on this page' At this stage, I have the value of 1 (being the last ten years) or 0 (being all records before ten years ago). Depending on which value I select, the visual slicer will show the according date range.

I'm struggling on what I need to do next. I've updated Bookmark 1 to have its 'Data' (which wasn't checked) and its 'Display' (which was checked) options both checked. I've checked the value of 1 for the Date Flag in the 'Filters on this page' and then updated Bookmark 1 and clicked saved. The saved state show just the last ten years (as expected). The data slicer visual still doesn't show any earlier dates (becuase I have the Date Flag set to 1 still).
If I uncheck the Date Flag then all dates show but I don't get the last ten years as default in the date slicer.

What am I not following? Thanks.

[v-mdharahman]

Hi @D_PBI,

Given your constraints especially the requirement to leave the Bookmark’s ‘Data’ option unchecked due to embedding behavior the approach using a DateFlag column won’t meet the need of defaulting the slicer to “last 10 years” while keeping earlier dates selectable because when you apply DateFlag = 1 as a page filter, it removes dates outside that range from all visuals including the slicer. And when you remove that filter to allow full date access, you no longer have a default selection in the slicer.

The issue is that Power BI slicers do not support dynamic default values out-of-the-box unless you use Bookmarks with 'Data' checked which you cannot or you handle the filter defaulting in the embedding layer with JavaScript via the Power BI JavaScript API.

Since you are embedding and bookmarks with 'Data' are off-limits, your best path is to control the filter dynamically via the JavaScript API at embed time. This gives you full slicer range, default selection to “last 10 years" and full user interactivity beyond the default

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[D_PBI]

@v-mdharahman - thanks for the response.
This using JavaScript embedded within PBI will be new to me. I don't know how to go about this but I don't believe I fully understand your suggestion. Are you suggesting:
1) Write JavaScript code inside the .PBIX file that will set default dates for the date slicer?
2) Leave the .PBIX alone. Embed the repot within the webpage, as it currently is, and then write JavaScript within the webpage's code to set default dates for the date slicer?

Which are you suggesting, or are you suggesting something else?  If you're suggesting Option 1) then do you have a link(s) that you can provide me with?  If you're suggesting Option 2) well out of my access (external company has built/maintains that webpage) and so I cannot created anything within it.
Thanks.

[v-mdharahman · Accepted solution]

Hi @D_PBI,

Writing JavaScript inside the .pbix file is not possible. Power BI Desktop doesn’t support embedding or executing JavaScript within the .pbix report itself. So there’s no way to apply default slicer filters via code inside Power BI.

I was referring to JavaScript used in the webpage where the report is embedded. The Power BI JavaScript API (called the Power BI Client SDK) allows developers to control certain aspects of a report at runtime like setting filters, switching pages, or changing slicer selections from the webpage that hosts the embedded report, not from inside the report itself.

So yes, this would involve leaving your .pbix file as-is, while writing JavaScript in the embedding webpage. Applying a filter programmatically using the Power BI Embed API after the report loads.

However in your case you mentioned that the webpage is built and controlled by an external company, and you don’t have access to inject JavaScript into it. That unfortunately means this solution is not available to you at this time.

Best Regards,

Hammad.

[D_PBI]

@Greg_Deckler - thanks for your reply. I had thought of using relative date but the user may want to filter by a specifc date range.

@bhanu_gautam - thanks for your reply. I'm attempting to implement it but am having difficulty. Please can you provide more guidance.
I have created the measure as you've stated and this sits in the date dimension table.

The Slicer visual which has the dimDisclosureDate[Date] field is shown below. You can see I've placed the newly created 'Date Flag' column in there and clicked on the value being '1' which will show the 01/01/2015 - 30/06/2025. You can see this date range is what is showing in the Slicer visual.

So far. This is what I'm wanting to happen. However, from here, I would like the report user to be able to use the Slicer visual to go back further in time (should there be earlier dates) and/or go forward in time (shoud there be later dates).
I'm not seeing how to do this following your suggestion, although I don't think I'm following it accurately.

Please can you help further?  Thanks.

[v-mdharahman]

Hi @D_PBI,

You are perfectly correct in your observation: by using the DateFlag = 1 filter within the slicer visual, you are limiting the slicer to displaying dates only within that time span. That's why users are not able to view earlier or later dates as they are being filtered out at the level of the slicer itself.

You can correct this by:

* Deleting the DateFlag filter from the Slicer visual. Click on the slicer. In the Filters pane, remove any filters on DateFlag.

* Instead, use the DateFlag = 1 filter at the page or report level (only as a default filter). Thus, the slicer still has all dates from 1997 to 2025, but the default selection upon report open will emphasize the last 10 years range.

* To preset the default date range (01/01/[TodayYear - 10] to Today) in the slicer on report load, attempt to use a Bookmark. Manually set your slicer to the preferred date range (e.g., 01/01/2015 through today). Create a Bookmark (View > Bookmarks pane > Add). Be sure to include "Data" and "Display" in the bookmark settings.

Next, assign this bookmark to open by default when the report is opened (if in Power BI Service, make it the default for users).

I would also take a moment to thank @bhanu_gautam and @Greg_Deckler, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[bhanu_gautam]

@D_PBI Add a calculated column to your dimDate table to flag the dates within the desired range (from 1st January 10 years back to today).

DAX
DateFlag =
VAR TodayDate = TODAY()
VAR StartDate = DATE(YEAR(TodayDate) - 10, 1, 1)
RETURN IF(dimDate[Date] >= StartDate && dimDate[Date] <= TodayDate, 1, 0)

Use the DateFlag column in your Slicer visual to filter the dates. Set the default filter to show only the dates where DateFlag is 1.

In the Slicer visual, set the filter to only show dates where DateFlag is 1. This will ensure that when the report is loaded, the Slicer visual shows the date range from 1st January 10 years back to today.

Ensure that the Slicer visual allows users to adjust the date range as needed. The downstream visuals will automatically reflect the changes based on the selected date range in the Slicer.

[Greg_Deckler]

@D_PBI Sounds like you want a relative date slicer: