cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Guru123
New Member

How to set default MAX date in Slicer

Dear Team,

I have a date slicer on the page, I would like to select automatically most recent date(Max date) in the slicer when users open the dashboard. Can please help to share the logic. Apprecite the help! 

 

Thanks

2 ACCEPTED SOLUTIONS

Thanks a lot, Sergii24. It works! I would like to show the maximum date as a date value,

example : Maximum date 14/09/2023 , is this possible, please. thanks

View solution in original post

I'm glad it helped, @Guru123 ! Unfotunately you can't add the date itself there as the whole concept is to make sure that your slicer value is static (so doesn't change after refresh). 

You can add a "selected period" label somewhere in your report to underline what date is selected.

Remember to mark answer as solution so others can easily find it if the face a similar problem 🙂
Have a great day! 

View solution in original post

8 REPLIES 8
Sergii24
Resolver III
Resolver III

 Hi @Guru123, filtering a dynamic date by default is a tricky thing! The biggest problem is not in defining a logic to select a specific date or date range, but in getting rid of previos label. For example, today your max date is 14/09/2023, so you creare a measure that gives you a desired result and the report is filtered as expected. However, tomorrow, when your measure will correctly point to 15/09/2022, the previous label of 14/09/2023 will remain selected (even if this date doesn't exist in your report anymore!). You'll need to select another value from a filter to make disappear "14/09/2023".

To overcome this problem, I would suggest you to create a calculated column in your Calendar Table, where for max date you'll return a label "Maximum date" and for the rest rows - corresponding date from a date column. In such you, "Maximum date" label will remain static (so there is no need to unclick it every day) but will point to different date depending on report refresh (remember that calcualted column value is calcualted when you refresh a report).

Sergii24_0-1694677941893.png

 

Sergii24_1-1694677993686.png

Calculated column code:

Date for filter = 
VAR _MaxDate = MAX( 'Calendar Table'[Date] )
RETURN 
    IF(
        'Calendar Table'[Date] = _MaxDate,
        "Maximum date",
        FORMAT( 'Calendar Table'[Date], "dd/mm/yyyy" )
    )

 

Thanks a lot, Sergii24. It works! I would like to show the maximum date as a date value,

example : Maximum date 14/09/2023 , is this possible, please. thanks

I'm glad it helped, @Guru123 ! Unfotunately you can't add the date itself there as the whole concept is to make sure that your slicer value is static (so doesn't change after refresh). 

You can add a "selected period" label somewhere in your report to underline what date is selected.

Remember to mark answer as solution so others can easily find it if the face a similar problem 🙂
Have a great day! 

Can you please elaborate this approach what you mean there?

 

I tried your above suggested solution but I want to use my slicer in "Between" slicer style since the date range is really long. Now because the calculated column now becomes a text column, it wouldn't work for this style.

 

If there is a way that I can manipulate tmy slicer to show the last date value by setting an underlying value or filter somehow in a between style slicer, I would be great.

Thank you 😊 

123abc
Memorable Member
Memorable Member

In Power BI, you can set a default maximum date (Max date) in a slicer by using the "Default Value" property of the slicer. Here's how you can do it:

 

  1. Select the slicer visual on your Power BI report page.
  2. In the "Visualizations" pane, make sure the slicer visual is selected (it should have a border around it).
  3. In the "Format" section of the "Visualizations" pane, expand the "General" section.
  4. Look for the "Default Value" property.

Now, to set the default maximum date, you have a few options:

Option 1: Fixed Date

  • To set a fixed maximum date, click on the "Fixed" option under "Default Value."
  • A calendar control will appear, allowing you to select the desired maximum date.

Option 2: Calculated Date

  • To set the default maximum date dynamically based on your data, click on the "Advanced" option under "Default Value."
  • In the formula bar, you can enter a DAX expression to calculate the maximum date. For example, if you want the maximum date to be the maximum date in your date field, you can use the following DAX expression:

=MAX('YourTable'[YourDateColumn])

 

  • Replace 'YourTable'[YourDateColumn] with the actual table and date column from your data model.

Option 3: Relative Date

  • If you want the maximum date to be relative to the current date (e.g., today's date), you can use a DAX expression like this:

=TODAY()

 

Choose the option that best suits your needs and enter the appropriate value or DAX expression. After setting the default maximum date, the slicer will automatically show that date when users open the dashboard in Power BI.

Remember to save your report after making these changes to preserve the default maximum date setting.

@Hi @123abc, I didn't know that default value option existis in format pane! Would you be so kidn to send a screenshot where I can find it? I've tried to follow your suggestion but even search doesn't provide me any value for a sting "default" within the list of available formatting items...

Sergii24_1-1694678336028.png

Thank you!

 

Well what he is expaining is what chatGPT says, but it doesn't exist, indeed! Neither in PowerBi Desktop nor in PowerBi Service, but I would very much wanted to be wrong.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors