Button to Select Most Recent Date in a Dynamic Slicer

[ayanke]

Greetings! 

I have been requested to create a button, called "Get Latest", that selects year and week number in their respective slicer panels. As the button's name suggests, selecting the button should make the most recent selection of year and week number (i.e., this week). To make a slicer selection from a button, I'd normally use a bookmark, but a bookmarked selection is always fixed to the values selected upon creation. My slicers for year & week number are dynamic, so the selection of the most recent year & week number will change as time progresses. Do you know how I can create a button that selects the most recent year & week number in a dynamically updated slicer?

Thanks, 

Andrew

[MFelix]

Hi  @ayanke ,

Believe that the best option is to have your slicer not having any selections and if this happens then you get the most recent data on the visualizations.

I don't know how your model is setup but let's assume you have a visualization with the sales on a card if you do a measure similar to this:

Sales =
SWITCH (
    TRUE ();
    DISTINCTCOUNT ( SlicerTable[Week] )
        = CALCULATE ( DISTINCTCOUNT ( SlicerTable[Week] ) ); ALL ( SlicerTable[Week] );
    CALCULATE (
        SUM ( Table[Sales] );
        Table[Week] = MAX ( Slicer[Week] )
            && Table[Year] = MAX ( Slicer[Year] )
    )
)

In this case if you have no selection or all the rows selected for week it will return sales for maximum week and year.

Be aware that this formula may not work properly since it was made by heart and this needs to be adjusted to your calculations.

Another option can be create a filtering measure that checks if all weeks/years or more than one is selected and return the latest values for dates and filter you page base on that.

And then create a button that reset your slicers.

Believe this is a better option than always selecting the latest week as you refer bookmarks are not dinamic.

[ayanke · Accepted solution]

Thank you @MFelix for the quick response. You definitely gave me a good starting point and came a solution, shown below. I had to make one modification to your pseudocode. The Table[Week]=MAX( Slicer[Week] ) part is not valid within the CALCULATE function because it throws an error related to using a boolean expression and table filtering at the same time.

My solution is the following:

Sales = 

VAR maxyear = MAX(Table[Year])
VAR maxweek= CALCULATE(MAX(Table[Week]),FILTER(Table,Table[Year]=maxyear ))

RETURN
 SWITCH(TRUE(),
    NOT(ISFILTERED(Table[Week])) && NOT(ISFILTERED(Table[Year])),
    CALCULATE(SUM(Table[Sales]),
        FILTER(Table,Table[Year]=maxyear && Table[Week]=maxweek)
        ),
    SUMX(Table,[Sales])
 )

[MFelix]

Hi  @ayanke ,

As I refered Iwas doing it by heart, and without any data just putting out some workaroudn, glad I could help achieve a good result.