Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
6 years ago

Set most recent date as default to slicer

I have a slicer that contains "Calendar Weeks" and currently the most recent date in the slicer is 27 April 2020, and would therefore like this to be th default date when the report loads, obviously ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

StuartSmith 

In fact, there no straightforward way to select a category by default. As a workaround,You could add an additional slicer by creating a column, and select "the latest date".

 

Lastest Date Slicer = IF(Table[Date] = MAX(Table[Date]),LASTDATE(Table[Date]))

 

 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
6 years ago

I figured out how to get the code working from the page https://powerpivotpro.com/2013/12/a-simple-trick-for-combating-stale-slicers/,

 

Latest_Date_Column = IF(Sheet1[Calender Week] = MAX(Sheet1[Calender Week]), "Latest", FORMAT (Sheet1[Calender Week], "DD/MM/YYYY"))
 

And getting the "latest" displaying in the drop down, but the other dates are not in order. I guess it becuase its formatted as text.  Any ideas how i can get the other dates in order?

 

 

 

  • Chevere4E's avatar
    Chevere4E
    New Member
    3 years ago

    You could use the format  "YYYY/MM/DD" and it will orderning by default

  • Marcelo_Vieira's avatar
    Marcelo_Vieira
    Frequent Visitor
    3 years ago

    StuartSmith this actually works, thks.

     

    Im using this trick for "current year" and "current month", as my published report has one slicer for each. I dont have a date table with days, so my example could help someone else.

     

    Also, its a good idea to show the user which are the "current" selections, somewhere on the report....that could be done in several ways, one of them using SELECTEDVALUE (TIME_TABLE[YEAR] ) used in a "CARD" visual.

     

    CURRENT YEAR = 
SWITCH(
    TRUE();
    TIME_TABLE[YEAR] = MAX( TIME_TABLE[YEAR] ); "CURRENT YEAR";
    "PREVIOUS (" & FORMAT( TIME_TABLE[YEAR]; "0000" ) & ")"
)

CURRENT MONTH = 
SWITCH(
    TRUE();
    TIME_TABLE[YEAR_MONTH_NUMBER] = MAX( TIME_TABLE[YEAR_MONTH_NUMBER] ); "CURRENT MONTH";
    TIME_TABLE[MONTH_TEXT_COLUMN]
)