Date Slicer Issue

[SDittmannFleet]

I created a custom column in my primary date table using this code:

Slicer Date = 
IF(
    [Date] <= TODAY(),
    [Date],
    BLANK()
)

The date slicer is set to this custom column. This worked fine when I first put it in. However, today I updated the data in the file, and the table itself shows the correct values. However, the slicer itself still showed "11/7/2023" as the end date after updating, even though dates through 11/22/2023 were available when I clicked on the date field.

What is supposed to happen is that the end date always shows the latest date from the date table, which itself goes back to 2035.

Why is this happening, and how can it be fixed?

[gmsamborn]

Hi @SDittmannFleet 

You can create a table in Power Query (LastRefresh) and a DAX measure (_Include) for filtering.

// Power Query (M) to create LastRefresh
let
    Source = DateTime.FixedLocalNow(),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Last Refresh"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Last Refresh", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date)
in
    #"Inserted Date"

// DAX measure for filtering
_Include = IF( SELECTEDVALUE( 'Date'[Date] ) <= MAX( 'LastRefresh'[Date] ), 1 )

I hope this helps.

[SDittmannFleet]

Thank you for responding. Isn't this kind of what I'm doing already? I have a "last refresh" type table already, and I could tie it to that, but the date table the slicer is tied to actually goes to 2035; I just want to limit what the users sees. And the YouTube video I got the measure from basically said this should just work, but for some reason it doesn't.

[parry2k]

@SDittmannFleet if you have already selected the value it will not default to the most recent date. There are other alternate to achieve this.

[SDittmannFleet]

OK, so what other ways would I be able to use here? B/c the measure works as expected, and I can pick the most recent date, but it will not default to the most recent date, which is what I was expecting.