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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
B_Real
Advocate IV
Advocate IV

select which filter to use

I have a date dimension that contains the date an event is due to fall on. I have created one additional column to flag if the date occurs within 7 days of today's date, and a second column to flag if the date occurs within 30 days of todays date, like so:

 

IsInNext30days.JPG

 

 

 

 

 

 

It's easy enough to use the 7 day and the 30 day column as a single filter (slicer) in a dashboard. What I really want is to let the user choose which filter they want, in one single slicer. I can display two separate slicers to show each filter, but this gets messy, takes up space, and lets them choose combinations of 7 and 30 days (and others) that don't make sense. 

 

A similar thing in Tableau is when you can use the date filters to choose '1w','1m','3m','6m','1y','5y'. 

 

Am I thinking about this all wrong?

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@B_Real

 

According to your description, it seems you want to achieve a last X period slicer to filter dates. In this scenario, you need to create a calculated table for each period, and union those calculated tables into a "DatePeriod" table. Then build the relationship to date table for filtering. For more details, please see a blog below:

 

POWER BI – TIME PERIOD SLICER FOR LAST 7 DAYS,LAST 30 DAYS..

 

Regards,

 

View solution in original post

4 REPLIES 4
v-sihou-msft
Employee
Employee

@B_Real

 

According to your description, it seems you want to achieve a last X period slicer to filter dates. In this scenario, you need to create a calculated table for each period, and union those calculated tables into a "DatePeriod" table. Then build the relationship to date table for filtering. For more details, please see a blog below:

 

POWER BI – TIME PERIOD SLICER FOR LAST 7 DAYS,LAST 30 DAYS..

 

Regards,

 

Two good answers there! I've chosen @v-sihou-msft's as the accepted solution because I found that post easier to implement, but I believe both will work. 

 

Thanks for the responses guys! 

Phil_Seamark
Employee
Employee

Dont forget that when you add a column which is DateTime format to the canvas and make it a Slicer you get this flexibility now, where the user can adjust the range to get any date range requred.

 

Slicer.png

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark
Employee
Employee

I have used a Date Range table with some success.  I build one that looks like this 

daterange.png

 

 

 

 

 

 

 

 

 

 

 

And then I create a measure on my date table that looks like this :

 

Date Range Filter = if(
MAX('Dates'[Date]) >= NOW() - MIN ('Date Ranges'[ID])
&& MAX('Dates'[Date]) < NOW()
---------------------------------------------------------
,1
,0
)

 

This should return a 1 or 0 that you can use as a filter on the visual.  eg, specifiy that the output of that measure should always be one.

 

Then you can add a slicer to the report page over the [Date Range] column.  This will allow the user to easily select several dynamic date ranges.  Just add to the table if you want to offer more interesting ranges.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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