Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

auto select max date from slicer

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 snap...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the replies from johnt75. Attaluri and Rupak_bi.

     

    Hi Anonymous ,

     

    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's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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.