Date slicer equal today and all dates before today

[ThomasWeppler]

Hi Power Bi community

I have a date slicer with all the dates from the starts of the calender table until today.

However each time I open the report the max date changes to the last date I picked and not today.
So when my end users come back to the report in five weeks the last date it not up to date.
Is there a way to make it so the default date picked is today or the max date in my calender table.

All help is greatly appriciated.

[bhanu_gautam · Accepted solution]

@ThomasWeppler , For this first create a date table

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), TODAY()),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

Then create a measure for maximum date

MaxDate = MAX('Calendar'[Date])

Then add slicer and use date column from calender table and change setting to use as relative date filter and select as day now you will always get latest date

[AmiraBedh · Accepted solution]

You need a Calendar table :

Calendar = 
ADDCOLUMNS (
    CALENDAR (DATE (2015, 1, 1), TODAY ()),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date])
)

Then create a measure :

MaxDate = MAX(Calendar[Date])

Another one to set the default the date:

IsToday = IF(TODAY() = [MaxDate], 1, 0)

and then :

DefaultDate = 
FILTER (
    Calendar,
    Calendar[Date] = TODAY()
)

[Idrissshatila]

Hello,

I made a video to show how to set today's date as default while showing other dates 

https://youtu.be/kVcStgZhnuw?si=__o61_izYQkikbn8

[Anonymous · Accepted solution]

Thank you @bhanu_gautam and @bhanu_gautam 

It's good that you provided a date table DAX expression that can get the real-time date.

Hi, @ThomasWeppler 

As Super User says, you need to create a real-time date table (with today). You are experiencing this situation when you automatically default to the date you selected for the last time you open the report every time you reopen the report. For this default behavior, you can choose bookmark as an alternative workaround. When you open the report, use bookmarks to reset your slicer to the maximum date of your date table.

In addition to this, you can enable the following feature when you publish a report to Power BI Service, which resets the slicer options when the end user closes the report:

When I open the report to change the slicer:

When I reopen the report, the slicer resets to the latest date:

Best Regards

Jianpeng Li

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

[AmiraBedh · Accepted solution]

You need a Calendar table :

Calendar = 
ADDCOLUMNS (
    CALENDAR (DATE (2015, 1, 1), TODAY ()),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date])
)

Then create a measure :

MaxDate = MAX(Calendar[Date])

Another one to set the default the date:

IsToday = IF(TODAY() = [MaxDate], 1, 0)

and then :

DefaultDate = 
FILTER (
    Calendar,
    Calendar[Date] = TODAY()
)

[ThomasWeppler]

Hi @AmiraBedh The problem is that if I add a filter where I set the date = max date then I cannot use the filter for anything since I need to be able to choose multiple dates I just wants the filters default to be the max date. I hope that makes sense.

[bhanu_gautam · Accepted solution]

@ThomasWeppler , For this first create a date table

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), TODAY()),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

Then create a measure for maximum date

MaxDate = MAX('Calendar'[Date])

Then add slicer and use date column from calender table and change setting to use as relative date filter and select as day now you will always get latest date

[ThomasWeppler]

Thanks for the quick answer @bhanu_gautam.
How do I change the setting to use a relative date filter?

[bhanu_gautam]

@ThomasWeppler < Select slicer and date column then go to settings