Dynamic Date Slicer - Default to Latest Available Date with Data

Power BI Date Slicer - Automatically Select Latest Date with Available Data (Composite Key Error & Incorrect Default Selection)

Hi everyone,

I'm trying to configure a native Power BI date slicer so that it automatically defaults to the latest available date with data after every dataset refresh.

For example:

Today's date: 28-Jul-2026

Latest data available in SQL: 27-Jul-2026

The slicer should automatically select 27-Jul-2026 whenever the report is opened or refreshed, because the views will be sent for automation.

Environment

Power BI Desktop Version: 2.155.756.0

Storage Mode: Import

Data Source: SQL Server (AWS RDS)

Tabular Editor 2

Calendar table is a DAX calculated table.

Latest Available Date

I first created a SQL query which always returns the latest available date from all tables containing report data.

LatestAvailableDate =
Sql.Database(ServerName, DatabaseName,
[
    Query="
    SELECT MAX(LatestDate) AS LatestAvailableDate
    FROM
    (
        SELECT MAX(CreatedDate) AS LatestDate FROM powerbi.BetDetails
        UNION ALL
        SELECT MAX(CreatedDate) FROM powerbi.DepositWithdrawDetails
        UNION ALL
        SELECT MAX(CreatedDate) FROM powerbi.PlayerDimension
        UNION ALL
        SELECT MAX(RegistrationDate) FROM powerbi.PlayerDimension
    ) t
"])

This returns a single row such as:

LatestAvailableDate
-------------------
2026-07-27 18:35:00

First Attempt

Calendar table:

Calendar =
ADDCOLUMNS(
    CALENDAR(TODAY()-450, TODAY()),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month No", MONTH([Date]),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "Month Year", FORMAT([Date], "MMM YYYY"),
    "YearMonthSort", YEAR([Date]) * 100 + MONTH([Date])
)

Created:

Slicer Display Date =
FORMAT('Calendar'[Date], "dd MMM yyyy")

Created:

Slicer Key =
VAR LatestDate =
    DATEVALUE(MAX('LatestAvailableDate'[LatestAvailableDate]))
RETURN
IF(
    'Calendar'[Date] = LatestDate,
    "__LATEST__",
    FORMAT('Calendar'[Date], "yyyymmdd")
)

Then in Tabular Editor 2 I configured:

Calendar[Slicer Display Date]
    →
Group By Columns
    →
Calendar[Slicer Key]

After doing this I received the following error: 

Column [Slicer Display Date] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

I tried removing the Group By property, recreating the columns, and applying Group By only at the end, but the same error always returned.

Second Attempt

I modified the Calendar table so that Slicer Display Date was created inside the Calendar table itself.

I also recreated Slicer Key several times using different DAX expressions including:

VAR LatestDate =
DATEVALUE(
MAXX(
ALL('LatestAvailableDate'),
'LatestAvailableDate'[LatestAvailableDate]
)
)

and other variations using CALCULATE() and MAX().

The composite key error still appeared every time after assigning Group By Columns.

Third Attempt

I then removed the standalone calculated column completely and generated both columns inside the Calendar table itself.

Calendar =
VAR LatestDate =
    DATEVALUE(
        MAXX(
            ALL('LatestAvailableDate'),
            'LatestAvailableDate'[LatestAvailableDate]
        )
    )

RETURN
ADDCOLUMNS(
    CALENDAR(TODAY()-450,TODAY()),
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"MMM"),
    "Month No",MONTH([Date]),
    "YearMonth",FORMAT([Date],"YYYY-MM"),
    "Month Year",FORMAT([Date],"MMM YYYY"),
    "YearMonthSort",YEAR([Date])*100+MONTH([Date]),

    "Slicer Display Date",
        FORMAT([Date],"dd MMM yyyy"),

    "Slicer Key",
        IF(
            [Date]=LatestDate,
            "LATEST",
            FORMAT([Date],"yyyymmdd")
        )
)

Then again in Tabular Editor:

Slicer Display Date
→
Group By Columns
→
Slicer Key

This time no composite key error occurred.

The Remaining Problem

Unfortunately, the required behavior still doesn't work.

Test 1

• Latest available date in SQL = 27-Jul-2026
• I manually selected 24-Jul-2026
• Saved the PBIX
• Closed Power BI Desktop
• Opened it again

The slicer still showed 24-Jul-2026 instead of automatically changing to 27-Jul-2026.

Test 2

I thought perhaps this only works in the Power BI Service.

So I:

• saved the PBIX with 24-Jul-2026 selected
• published the report
• refreshed the semantic model

Instead of selecting the latest available date, the slicer reset to the first date in the calendar (Sunday, May 04, 2025).

Reference Slicer Not Defaulting to Latest Date in Power BI

The approach I'm trying to implement is from this Microsoft Fabric Community post: Slicer Not Defaulting to Latest Date in Power BI 

My Question

Has anyone successfully implemented this solution for a native Power BI date slicer? Would you recommend another solution? The main goal is a setup that is already workingbuilt into Tableau: automatically showing the latest available date containing data instead of the latest day or the latest chosen day in the slicer for future automated report purposes.

If so:

1. Is there an additional configuration step that is missing?
2. Is this expected to work in both Power BI Desktop and Power BI Service?
3. Is there something incorrect in my implementation that causes the slicer to remain on the previously selected date or reset to the earliest date after publishing?

Any guidance would be greatly appreciated.

Thank you!