Set a Dynamic MAX date

[kaweckil]

Hi,

I was wondering if PowerBi supports a measure to dynamically update the max date in my dates slicer. That is, the max date should set to the most recent date in my data without requiring the user to move the slicer.

Thank you for any tips and suggestions! 

[lbendlin · Accepted solution]

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[Anonymous · Accepted solution]

Hi @kaweckil 

Thank you very much lbendlin for your prompt reply.

lbendlin's answer fits the situation you're looking for. Have you solved your problem? If not, allow me to add some details.

Create a column.

Max date = 
IF(
    'Table'[Date] = MAX('Table'[Date]),
    "Max date",
    FORMAT('Table'[Date], "mm/dd/yyyy")
)

Solved: How to set default MAX date in Slicer - Microsoft Fabric Community

To create a slicer, select Max date. In this case, the slicer will be updated based on your date, with the default "Max date" selected.

Regards,

Nono Chen

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

[Mohammed_Sami2]

Create the Calculated Column:-
IsLatestDate = 
IF(
    'Date_Table'[Date] = MAXX(ALL('Date_Table'), 'Date_Table'[Date]),
    "Latest",
    FORMAT('Date_Table'[Date], "yyyy-MM-dd")
)


Sort the Column:-
Go to the Modeling tab, select the new column, and choose Sort by Column. Select the original date column.


Add to Slicer:-
Add the IsLatestDate column to your slicer. Set the slicer to “Latest” to dynamically update to the most recent date.

[lbendlin · Accepted solution]

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[Ranya]

Yes, Power BI does support dynamically updating the max date in a date slicer to reflect the most recent date in your data without requiring user interaction. You can achieve this by using a combination of DAX measures and the relative date slicer.

### Here's how you can do it:

1. **Create a Date Table**: Ensure that you have a date table that is marked as a Date table in your model. This table should have a continuous range of dates covering your data's time span.

2. **Create a Measure for Max Date**: Create a measure that calculates the most recent date in your dataset. You can do this using the `MAX` function.

```DAX
MaxDate = MAX('YourDateTable'[Date])
```

3. **Use the Relative Date Slicer**:
- Instead of using a standard date slicer, you can use a **Relative Date Slicer**. This slicer allows you to filter data based on relative time periods, such as "Last 1 day," "Last 1 month," etc.
- Set the slicer to show data relative to today, such as "Last 1 day" or "Last 1 month." The slicer will automatically adjust to always show the most recent data based on the current date.

4. **Dynamic Title or Card**:
- You can create a card or a title that dynamically shows the most recent date in your data using the `MaxDate` measure created earlier. This will give users a clear indication of the date range they are viewing.

```DAX
DateDisplay = FORMAT([MaxDate], "MMMM DD, YYYY")
```

5. **Custom Visual Slicer (Optional)**:
- If you need more advanced slicing capabilities, you can look into using a custom visual slicer like the **Smart Filter by OKViz**. These allow for more sophisticated filtering and can be customized to always include the most recent date.

### Summary
By using a relative date slicer, Power BI will always display data up to the most recent date automatically, without the user needing to adjust the slicer. The `MaxDate` measure can be used to display or verify the date range being used.