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

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's avatar
jdbuchanan71
Super User
6 years ago

Do you have a date table you are using?

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.