auto select max date from slicer

[alicia_pbi]

Is there a way to default a date slicer in a report to select the max date?  I have a report that I am presenting with a source from SQL.  I want the report to auto select the max or most recent snapshot date so that I don't have to go to the workspace and change after every new snapshot.  I know I can use advanced filtering on the filter but I want the end user to always see the latest date on the slicer with the ability to use the slicer to see previous snapshots.  Is this possible?

[Anonymous · Accepted solution]

Thanks for the replies from johnt75. Attaluri and Rupak_bi.

Hi @alicia_pbi ,

You can use preselected slicer to achieve this effect:

I create simple test data:

Create a measure:

Pre selection = 
VAR _select=SELECTEDVALUE('Table'[Date])
RETURN
_select=TODAY()

Create a calculated table:

_PreselectedSlicer = DATATABLE("IsDirty", BOOLEAN ,{{False()},{True()}})

Result:

Best Regards,
Zhu

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

[johnt75]

Yes, with some setup. You need to create a copy of the column you want to slice by, lets assume its called 'Table'[Datestamp]. You would create a new calculated column in the same table like

Datestamp for slicer =
VAR LastDatestamp =
    CALCULATE ( MAX ( 'Table'[Datestamp] ), REMOVEFILTERS () )
VAR Result =
    IF (
        'Table'[Datestamp] = LastDatestamp,
        "Most Recent",
        FORMAT ( 'Table'[Datestamp], "dd/mm/yyyy" )
    )
RETURN
    Result

Hide this new column from users in report view.

Open Tabular Editor, select the 'Table'[Datestamp] column and under Options set "Group by columns" to include 'Table'[Datestamp for slicer].

Now when you use 'Table'[Datestamp] in the slicer, Power BI will actually store the value of the new 'Table'[Datestamp] column, so if you have selected the most recent date it will store "Most recent". When your report refreshes, "Most recent" will now equate to the most recent datestamp and so that is what will be selected in the slicer.

You should be aware that this will break in Excel if users try to use 'Table'[Datestamp] in a report. If that happens you can create 2 new columns, one an exact replica of 'Table'[Datestamp] which you will only use in the slicer and the other as described above.

[Attaluri]

You can create a flag column in date table. It will show "Latest Date" for current date and for rest of the rows it just shows the dates. You can use this column as slicer and fillter on latest Date. Using this approach every time you open the report you see latest date selected in report. 

[Rupak_bi]

Hi @alicia_pbi ,

Inside Slicer its not possible (to the best of my knowledge). But there is an alternet way to achive this.
1. Create a measure like below and use that measure to filter your report. 
Max_Date = selectetvalue(table[date]),max(table[date]))
2. keep the slicer sepatate with the same date column.
by this way the measure will select the max date if nothing is selected in the slicer, else it will select the slicer date.