Dynamic date slicer with default starting point and ongoing ending point (yesterdays date)

[RaMiRo87]

Hi all,

I need a dynamic date slicer which always shows me the start of the reporting year as the starting point and the end date is yesterday's date. This is because the data is always from the previous day, not up to date.

In other words, today is 11.04.2024, so the slicer should display 01.01.2024 as the start time by default and 10.04.2024 as the end time. If we then jump to the year 2025, the start time of the slicer should automatically be 01.01.2025 from then on, etc.

Is there a solution exclusively via DAX or do I actually have to build some code in M for this?

Thank you very much for your responses in advance!

Cheers,

Ramon

P.S. I already have a relatively extensive date table that contains different flags and dates.

[johnbasha33 · Accepted solution]

@RaMiRo87 

You can achieve this dynamic date slicer behavior using DAX expressions in Power BI without needing to resort to M code. Here's how you can create a dynamic date slicer:

1. **Create Measures for Start and End Dates**:
First, create measures to calculate the start and end dates based on your requirements.

```DAX
StartDate = DATE(YEAR(TODAY()), 1, 1)
```

```DAX
EndDate = TODAY() - 1
```

The `StartDate` measure returns the first day of the current year, and the `EndDate` measure returns yesterday's date.

2. **Create a Slicer**:
Add a slicer visual to your report and use the date column from your date table as the field for the slicer.

3. **Set Default Slicer Values**:
Set the default values for the slicer to be the values of the `StartDate` and `EndDate` measures. You can do this by selecting the slicer, going to the Format pane, and configuring the Start and End dates under the "Default settings" section.

4. **Testing and Adjustments**:
Test the slicer to ensure that it defaults to the start of the reporting year and yesterday's date. Also, verify that it updates dynamically when you navigate to different years.

By following these steps, you can create a dynamic date slicer in Power BI that always defaults to the start of the reporting year and yesterday's date, ensuring that your data is filtered appropriately based on your requirements.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous · Accepted solution]

Hi @RaMiRo87 ,

Thanks @johnbasha33  for the quick reply.

One thing I need to clarify is that you can't find the start date and end date settings in the formatting pane.
You can create a slicer table.

Slicer table = CALENDAR(DATE(YEAR(TODAY()), 1, 1), TODAY() - 1)

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[johnbasha33 · Accepted solution]

@RaMiRo87 

You can achieve this dynamic date slicer behavior using DAX expressions in Power BI without needing to resort to M code. Here's how you can create a dynamic date slicer:

1. **Create Measures for Start and End Dates**:
First, create measures to calculate the start and end dates based on your requirements.

```DAX
StartDate = DATE(YEAR(TODAY()), 1, 1)
```

```DAX
EndDate = TODAY() - 1
```

The `StartDate` measure returns the first day of the current year, and the `EndDate` measure returns yesterday's date.

2. **Create a Slicer**:
Add a slicer visual to your report and use the date column from your date table as the field for the slicer.

3. **Set Default Slicer Values**:
Set the default values for the slicer to be the values of the `StartDate` and `EndDate` measures. You can do this by selecting the slicer, going to the Format pane, and configuring the Start and End dates under the "Default settings" section.

4. **Testing and Adjustments**:
Test the slicer to ensure that it defaults to the start of the reporting year and yesterday's date. Also, verify that it updates dynamically when you navigate to different years.

By following these steps, you can create a dynamic date slicer in Power BI that always defaults to the start of the reporting year and yesterday's date, ensuring that your data is filtered appropriately based on your requirements.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Nitinmathur]

Hello @johnbasha33 

There is no such Default Value setting under format option of a slicer in power BI to set Start Date and End Date value as mentioned in #3 in your post.

3. **Set Default Slicer Values**:
Set the default values for the slicer to be the values of the `StartDate` and `EndDate` measures. You can do this by selecting the slicer, going to the Format pane, and configuring the Start and End dates under the "Default settings" section.

Could you plz explain where is this setting, did you use any custom visual import like Date Picker?