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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
HassanAshas
Helper V
Helper V

How to modify and improve the visual of Relative Date Filter

I have a situation where I have to provide user with the option to filter the data by the Last 'n' months

 

I created a slicer on the Date column and changed it to Relative Date Filter. This accomplishes my goal but the slicer looks very ugly. It looks something like this, 

 

HassanAshas_1-1671369615550.png

 

 

  • First, the slicer provides option to filter data by multiple items (Last n days, years, months etc.). I want to change that to only Last Months (Calendar), and remove the other options. How can I accomplish this? 
  • Second, I don't want to keep the First slicer option in it (that is, the choice between This/Last/Next). I only want the Last. Is there any way to modify that? 

I thought of an idea that maybe I could somehow make a relation between a table created by myself and use it to filter by the last months. For example, I made a table by myself and made a slicer on it like this, 

 

HassanAshas_0-1671369567351.png

 

But I can't seem to understand how can I combine this with that Slicer of Relative Date Filter and then filter data accordingly. 

 

Can anyone help on this? 

 

Here is the workbook download link if needed: https://drive.google.com/file/d/1IezI9ghqj_u7mfRP_NfivJGnV8kdVY0f/view?usp=sharing

1 ACCEPTED SOLUTION

You can create a What If Parameter with your desired month number range, which will give a slicer with a single value box. You can then use the auto selected value measure auto created and incorporate that into your measure to show that many previous months.

ppm1_0-1671389687104.png

 

Pat

 

Microsoft Employee

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

You could add another table as you suggest, but it would be easier to add a MonthsFromNow column to your Date table.

 

Months From Now = 

DATEDIFF ( TODAY (), [Date], MONTH )
 
ppm1_0-1671372404668.png

Pat

 

Microsoft Employee

Thanks a lot for this suggestion. 
But this again gives the same problem. It really looks terrible to have "two" fields for a slicer. I would rather have just one field and use that. 

 

Now, I can of course make a table and attach it to this "Months for Now" column in Data Model, but it wouldn't work (because if I select Last "6" months, it will only filter out the Months where the "MonthsFromNow" has the value "6" and not the other months) 

You can create a What If Parameter with your desired month number range, which will give a slicer with a single value box. You can then use the auto selected value measure auto created and incorporate that into your measure to show that many previous months.

ppm1_0-1671389687104.png

 

Pat

 

Microsoft Employee

Oh my God! Thank you so much! Never thought that we could tackle this problem with this! 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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