Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
6 years ago
Solved

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) ...
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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.

admin_xlsior's avatar
admin_xlsior
Post Prodigy
6 years ago

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

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

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's avatar
    Felix7216
    Regular Visitor
    3 years ago

    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