How to automatically select today as the latest date in PowerBI Between Slicer?

[cmajor]

My end user likes the style of the report slicer set to Between as it provides the flexibility needed to select dates within the report. However, this report updates daily and she would like the default selection to select dates of the current year (1/1/23 - 4/27/23). The current state of the report upon refresh still just shows yesterday as the max date of the data by default, requiring her to manually select the latest date. This becomes more of an issue later in the year, or when next year comes along. Is there a fix to this?

[Idrissshatila]

Hello @cmajor ,

check if this may help
https://youtu.be/kVcStgZhnuw?si=__o61_izYQkikbn8

[cmajor]

I am having issues with step 7 of your solution. I do not see an "End Date" field to use the "Today" measure in. The slicer just shows the available dates of my Date column. 

[Neka]

I am having the same issue. Did you ever find a solution?

[cmajor]

Not a straightforward one. I had to use bookmarks to have a default selection go to today's date (no slider) and another that opened up the slider visual. That way when the user first opened the report, I could specifiy what they see before any interactions have taken place, but then they could still slide the dates as they please (if that decision has been made).

[Ghhousuddin]

To automatically select today's date as the latest date in a Power BI between slicer, you can use a combination of DAX and Power Query steps.

Here's how to do it:

1. Create a measure in the model that returns today's date:

```
Today = TODAY()
```

2. Create a new query by going to "Home" > "New Source" > "Blank Query".
3. In the "Query Editor", click on "View" > "Advanced Editor".
4. In the "Advanced Editor", paste the following M code:

```
let
Today = DateTime.LocalNow(),
StartOfYear = Date.StartOfYear(Today),
EndDate = Date.AddDays(Today, -1),
Source = #table({"Start Date", "End Date"}, {{StartOfYear, EndDate}})
in
Source
```

This code creates a table with two columns, "Start Date" and "End Date", that represent the default date range of the slicer. The "Start Date" is set to the start of the current year, and the "End Date" is set to yesterday's date.

5. Click on "Close & Load" to load the query into the model.
6. Create a between slicer using the "Start Date" and "End Date" columns from the new query.
7. Set the default selection for the slicer to "Between" and use the "Today" measure as the maximum value for the "End Date" field.

To do this, go to the "Visualizations" pane, click on the slicer, and expand the "Format" section. Under "Default selection", choose "Between" and set the "End Date" field to use the "Today" measure.

```
Today = TODAY()
```

This will ensure that the slicer automatically selects today's date as the latest date when the report is refreshed.

With these steps, the between slicer will default to showing the date range of the current year, with today's date as the latest date, when the report is refreshed.

[Neka]

I've seen these instructions before but whenever I go to the Format section of the slicer and select "between" there is no "End Date" field. Are there any screenshots you can provide to find this? Thank you.