cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin_xlsior
Post Prodigy
Post Prodigy

filter date slicer before today

Hi,

 

Is there a way the date in slicer automatically set Max value with today's date, but I do not want to use "Relative date include today" setting, since I still need the null date (1900/01/01)

 

Thanks,

 

1 ACCEPTED SOLUTION

If you add a column to your date table like so

DateOffset = 
DATEDIFF(TODAY(),'Date'[Date],DAY)

That will show 0 on today, -1 for yesterday, etc.

Then you can use this column as a filter on your date slicer and set it to <= 0 OR blank:

DateFilter.jpg

I think that will give you what you are looking for.

View solution in original post

6 REPLIES 6
jdbuchanan71
Super User II
Super User II

Hello @admin_xlsior 

I'm assuming you are showing a measure in a visual that has data against the null date?  You can apply that measure to your date slicer as a filter and set it to Is Not Blank which will then only show dates with data in the slicer.  Will that work for you?

Hi, 

Actually no, I just want the date slicer always point or fill in with today's date in its Max value (To date)

But yes, the reason I do not want to use Relative Date because I have data which is Null, so it will be filtered out if I'm using Relatif date. I cannot say relative in the last 200 years because the allowed value is 1-100.

 

Thanks,

Do you have a date table you are using?

Yes, the slicer use Date table can be up to year 2154

If you add a column to your date table like so

DateOffset = 
DATEDIFF(TODAY(),'Date'[Date],DAY)

That will show 0 on today, -1 for yesterday, etc.

Then you can use this column as a filter on your date slicer and set it to <= 0 OR blank:

DateFilter.jpg

I think that will give you what you are looking for.

View solution in original post

If you are using Power BI Date/Calendar table then change the Date table as below.

 

DATES =
     Var MinDateFact = MIN( FactTable[Date] )
     VAR MaxDateFACT = TODAY()-1
RETURN
       CALENDAR( MinDateFact, MaxDateFACT ).
and if you use this date column in the slicer, Sliceer will only displat one day before.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors