Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

7 REPLIES 7
jdbuchanan71
Super User
Super User

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.

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
Not applicable

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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.