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
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?

 

 

 

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]
)