Slicer: Default to latest date in data set

[SokMunki]

Hi all! This feels like it should be easy, but I can't figure it out and haven't found a post that seems to address the issue...

We have a table that has a rolling 12-month set of data and a "report date" that is an end-of-month value. We have report data for 09/30/2022, 10/31/2022, 11/30/2022, and so forth. Soon we'll add 12/31/2022 to the report when we start getting line items for the current month. 

I want the slicer to default to the MAX report date in the data set. I figure having something like "Default Value of Slicer" = (some measure) ought to do it, where (some measure) is MAX of the report date field, but I can't figure out how to do that. 

Obviously, the users should be able to select earlier months as desired, but when the dashboard first opens it would default to the last available report month -- "November 2022" currently, "December 2022" when that data appears, for instance. 

Help? Thanks in advance! 

[PraveenPlus]

The simplest solution for this would be to just unselect the date field thats on the slicer (listed under the tables in the Data Tab) and then select it back again before saving the dashboard. Essentially remove the date field on the slicer and add it back again.

This will reset any pre-selected values in the slicer while preseving other settings on the slicer. 

The key is to not drag the slider or select any values from the slicer before saving. 

Hope this helps !

[v-zhangti]

Hi, @SokMunki 

You can try the following methods.
Sample data:

Create a new table for a date slicer.

Slicer = VALUES('Table'[Date])

 Measure:

Measure = 
Var _maxdate=CALCULATE(MAX('Table'[Date]),ALL('Table'))
Return
IF(SELECTEDVALUE('Slicer'[Date])=BLANK(),_maxdate,SELECTEDVALUE(Slicer[Date]))

Measure 2 = IF(SELECTEDVALUE('Table'[Date])=[Measure],1,0)

Put Measure 2 in the filter of the view you want to display and set it equal to 1.

When the slicer is not selected, the value of the maximum date is displayed by default.

When the slicer has a selection, the selected date and value are displayed.

Best Regards,

Community Support Team _Charlotte

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

[SokMunki]

Unfortunately this didn't seem to work out when the fields themselves (the date, Measure 2) weren't displayed in the visual. I'm not sure if there's a problem with how I've got the tables related in the back end. I think this is very clever, just isn't working for what I need.

[SokMunki]

Thank you! I'm trying this right now. I'm using Direct Query, so creating "Table" isn't possible, but I'm seeing if I can replicate using SQL views. So far I'm getting some odd results where Measure 2 = 1 for multiple dates, but that may be something I'm screwing up. I will let you know how it goes.

[amitchandak]

@SokMunki , As of now you have to create a column

if(Date[Date] = max(Table[Date]) , "Max Date", Date[Date]& "" )

and sort it on the date column and save on max date

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

[PowerBI_Medvid]

Hi

Isn't there really a better way for PowerBi to better display the last month in the data set on Slicer?

[SokMunki]

If I'm understanding this correctly,  the slicer wouldn't actually show "November 2022" as the selectable item but rather something like "Most Recent Month", which seems clunky. (For several of my reports there's a delay between the first of the month and getting data for the month because of some auditing/editing, so "This Month" would be misleading.) 

However, this will probably solve the immediate need of setting the default filter to MAX(Available Data Date), so I will try this if I can't get v-zhangti's solution to work. 

Thank you!

[DataFunness]

Were you able to get something like this to work?  If so, could you provide how you did it?