Forum Discussion
Button to Select Most Recent Date in a Dynamic Slicer
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]) )
Super UserHi 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.
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])
)