Slicer to default to latest date based on filtered date range

[buttercream]

Hi,

I have a date column that refreshes once a month.  I would like to set up a slicer for this date column and make the latest date the default value.  But I also have a page filter for the same date column so the default latest date must be based on the date range from this filter.

[OwenAuger · Accepted solution]

Hi again @buttercream 

Determining the "current" date would to involve a measure in some way rather than a column, because columns are fixed on refresh and don't update based on filters in a report.

The challenge with the core slicer visuals is that they don't currently allow dynamic selection. The literal value selected remains selected until changed.

To help suggest some suitable ideas, what is the overall behaviour you want on the page? Do you always want a single date selection for example?

In the meantime, I don't think I have a great solution, but here are some other ideas (PBIX attached):

1. Use a Chiclet slicer for Date (limited to button style):

(a) Sorted descending by Date

(b) Multiple selection disabled

(c) Forced selection enabled

The latest date shown on the slicer (limited by other filters) should always be selected.

While some of the newer core slicers (Button Slicer and List Slicer) have a "Force selection" option, it doesn't work in the same way, and won't update automatically to choose the earliest available value but instead becomes stuck on the last selection.

2. Use a calculation group & field parameter to allow switching between Latest Date & Date Selection

This method is honestly a bit tedious and not sure if it's worth the effort.

(a) Create a calculation group as follows (Tabular Editor 3 DAX script):

--------------------------------------
-- Calculation Group: 'Date Filtering'
--------------------------------------
CALCULATIONGROUP 'Date Filtering'[Date Filtering]

    CALCULATIONITEM "Latest Date" = 
        VAR LatestDateReportFilter =
            CALCULATETABLE ( LASTDATE ( 'Date'[Date] ), ALLSELECTED ( ) )
        VAR Result =
            CALCULATE ( SELECTEDMEASURE ( ), LatestDateReportFilter )
        RETURN
            Result
        , FormatString = SELECTEDMEASUREFORMATSTRING ()

    CALCULATIONITEM "Select Date" = SELECTEDMEASURE ()
        , FormatString = SELECTEDMEASUREFORMATSTRING ()

(b) Create a field parameter called 'Date Field Parameter' that allows selection of 'Date'[Date] or "No selection":

Initially add just 'Date'[Date] to the field parameter then edit the DAX expression as follows:

{
    ( "Date", NAMEOF ( 'Date'[Date] ), 0 ),
    ( "No selection", "DUMMY", 1 )
}

(c) For the Date slicer on the page, use the column 'Date Field Parameter'[Date Field Parameter].

(d) Create a measure Date Slicer Field Parameter Flag

Date Slicer Field Parameter Flag = 
VAR DateFileringSelection =
    SELECTEDVALUE ( 'Date Filtering'[Date Filtering] )
VAR DateFieldParameterSelection =
    MAX ( 'Date Field Parameter'[Date Field Parameter] )
VAR ResultBoolean =
    IF (
        DateFileringSelection = "Latest Date",
        DateFieldParameterSelection = "No selection",
        DateFieldParameterSelection = "Date"
    )
VAR Result =
    INT ( ResultBoolean )
RETURN
    Result

(e) Place a Top N filter on the slicer as follows:

(f) Add a single-select slicer for the calculation group.

Regards

[buttercream]

The chiclet slicer is exactly what I needed.  Appreciate the sample file, saved me a lot of time.  Thank you very much.

[OwenAuger]

Hi @buttercream 

To my knowledge the only generally-available free slicer visual that allows a default selection that depends on filters in the report (such as the page-level filter) is the Preselected Slicer custom visual.

I recall that there has been mention of similar functionality coming to the core visuals, hopefully sooner rather than later! 🙂

I have attached a small PBIX showing how you could use the Preselected Slicer visual. You can also download the sample report from the visual developer. It requires a boolean-valued measure to determine the default selection and a column containing true/false to control the "dirty status".

The display options for this visual are a possibly more limited than other slicer visuals so not sure if it will meet your needs.

Regards

[buttercream]

Thanks for the suggestion.  Is there a measure or column formula where I can relabel the latest date as "Current" so I can set the regular slicer to that as the default?  I can't seem to get it to recalculate the "Current" date based on the selected date range from the filter panel.