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.
