automatically select latest date in slicer when value is not 0

[dw700d]

Good day all,

I would like to have a slicer that  automatically selects the most recent date in my data set. For instance in the visual below the slicer should automatically select 12/31/22 since this is the most recent date. Once 1/31/23 is added the slicer should automatically select 1/31/23. What kind of measure can I create to accomplish this?

There is additional criteria that would be helpful if possible but if too complicated I don’t need to have. As you can see in the visual below 12/31/22 has a 0 entry. This means the data for 12/31/22 has not been loaded yet. I would like to have a slicer that automatically selects the latest date in my data set with a value other than 0. For instance in the visual below the slicer should automatically select 9/30/22 since 12/31/22 has a value of 0. Once 12/31/22 has a value other than 0 then the slicer will select 12/31/22. What kind of measure can I create to accomplish this?

[v-jayw-msft · Accepted solution]

Hi @dw700d ,

Create a measure like below:

Measure = 
var maxnot0 = CALCULATE(MAX('Table'[date]),FILTER(ALLSELECTED('Table'),'Table'[value]<>0))
return
IF(SELECTEDVALUE('Table'[date])=maxnot0,1,0)

Add it to visual filter and set value = 1.

Please note that the slicer cannot be automatically selected at any time.

This method only makes the slicer to display the most recent date that is not 0.

[timg]

Hi,

You could create a calculated column in which you replace the date with either  "Today" or "Other" based on the day it is (you can use TODAY() for this). If you then use this column as a slicer you can always filter your results based on the current day. Below I placed an example:

Hope that helps!

Regards,

Tim

[Anonymous]

Hi

Use the sorted option wusing the 3 dots and choose sort descending