filter date slicer before today

[admin_xlsior]

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,

[jdbuchanan71]

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?

[admin_xlsior]

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,

[jdbuchanan71]

Do you have a date table you are using?

[admin_xlsior]

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

[jdbuchanan71 · 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:

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

[Felix7216]

Hey jdbuchanan71,

Thanks heaps - you are a genius!

I so needed this to do fiscal year to date Budget against year to date Actuals.

So I still have Date as >= 1 July

BUT now with your help, the DateOffset is set <1

Problem fixed. Thanks for posting this

[Anonymous]

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.