Select latest date in Slicer

How to select the latest date by default in a slicer?

Hi @Anu,

Create a measure like this to harvest the latest date from a slicer:

Date Harvest Max = 
    CALCULATE(
        MAX( Dates[Date] ),
        ALLSELECTED( Dates[Date] )
    ) 

.
and check out this resource

I hope this is helpful.

What I mean is, from the list of dates in my slicer, it should always by default select data for the latest date,

What kind of slicer?
And do you always want to select a single date?

Normal Slicer. I always want to select the latest date available.

@Anu,

See if this does the trick:

Default Slicer Value = 

VAR MaxDate = 
CALCULATE(
    MAX( Dates[Date] ),
    ALL(Dates[Date] )
)

VAR Selected =
SELECTEDVALUE(
    Dates[Date],
    MaxDate
)

RETURN
Selected 

With a value selected:
image

With no value selected:
image

I hope this is helpful.

  • Brian
1 Like

Hi @Anu, we’ve noticed that no response has been received from you since the 15th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

where should I put this measure?

Create the measure in the same dataset and then use that measure as the slicer value

I am not able to add measure as slicer value

Thanks for posting your question @Anu. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

This is almost what I need except MaxDate needs to be today’s date not the max date in the date table. I’m using this in a balance sheet report with a ‘before’ date slicer. The before date needs to dynamically change to the current date without the user having to change it.

Two options come to mind. Do you have a Last Refresh date in your model? If not you can find that in the M Showcase category.

Then wrap FILTER around ALL/ALLSELECTED Dates where:

Dates[Date] = [Last Refresh Date]
OR
Dates[IsAfterToday] = FALSE

I hope this is helpful.

Thanks @Melissa. I’ll check it out and update.

This is the slicer I need to default to the current days date. I’m just now sure how to do that based on what you mentioned above. It needs to always be up to the current date in the PBI service. The only way I can do that is to remove it and use a ‘DayOffset’ value of <= 0 but then the date cannot be changed.

image

Hi @mkaess,

Okay, this more is tricky then I thought… Only thing that really seems to work is adding IsAfterToday to the Filter Pane… Where users can enable or disable the selection.

image

1 Like

Thanks, I do have something like that in my date table. In this case, I used a DayOffset in the filter pane set to <= 0. The only workaround I’ve found is to create a duplicate page and remove the date slicer. That way, the report is automatically updated to the current date. Users can subscribe to that page. When they receive the email, it’s up to date. The other version is for the accounts staff so they can change the dates as needed. Clunky but it works.