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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Filter 2 Relative Dates

Is there a way/visualization that enables you to filter between 2 relative dates in Power BI without a DAX expression? I want to avoid DAX as we're looking for a simple slicer visual that can enable us to slimply move between 2 relative dates.

 

Example: on Slicer 2.1, If I select 2 dates they stay the same since they are not relative. But we want to select 2 dates: 6 months in the future and 12 months in the future. Is there any visual or way to achieve this?

 

Thanks,

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

We can achieve your goal by build a Calendar Table.

Calendar = CALENDARAUTO()

Build a relationship between Date in Table and Date in Calendar Table.

1.png

Then use this table to build a Slicer.

When you want to see the data between the next 6 month and the next 12 month, you can select the day between 2021/2/28 and 2021/8/31.

Result:

1.png 

Currently, we can't use relative date slicer or filter to get the date between the dates in the future in power bi.

You can vote for this function in this idea: Idea

If this idea is not suitable for you , you can submit a new idea as well.

You can download the pbix file from this link: Filter 2 Relative Dates

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

6 REPLIES 6
TomSmith
New Member

The way I solved this, was to add an extra column (should be able to do exactly the same with a measure but it wasn't working for me so I had to add a column), and the DAX I used was ShiftedDate = DATEADD('TableName'[DateField],6,MONTH), then I added that to the page filters and selected "in the last" and 6 and months - and this then gives you between 6 and 12 months. 

v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

You can achieve your goal by creating a relative date slicer or filter.

I build a table to have a test.

1.png

1.    Relative date slicer:

Build a Slicer by Date value and select Relative Date.

2.png

If you want to get the date in the future 6 month, you can select Next 6 Month(Calendar).

Result:

3.png

Relative date filter:

4.png

Click apply filter.

Reuslt:

5.png

For more info about relative date slicer or filter, refer to this link: Creating a relative date slicer and filter in Power BI

You can download the pbix file from this link: Filter 2 Relative Dates

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Anonymous
Not applicable

Hi Rico,

What I actually mean is to filter between to dates in the future. For example, between the next six months and the next 12 months. Not just one of them.

 

Thanks,

Rashid 

Hi @Anonymous 

We can achieve your goal by build a Calendar Table.

Calendar = CALENDARAUTO()

Build a relationship between Date in Table and Date in Calendar Table.

1.png

Then use this table to build a Slicer.

When you want to see the data between the next 6 month and the next 12 month, you can select the day between 2021/2/28 and 2021/8/31.

Result:

1.png 

Currently, we can't use relative date slicer or filter to get the date between the dates in the future in power bi.

You can vote for this function in this idea: Idea

If this idea is not suitable for you , you can submit a new idea as well.

You can download the pbix file from this link: Filter 2 Relative Dates

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Anonymous
Not applicable

Yes, I submitted that idea. It would be really helpful if that functionality was added. 

Hi @Anonymous 

Please mark the helpful answer as a solution. If you mark the answer as a solution, this post will be easier to others to find. And other people could vote up your idea as well, so this function may be realized faster.

 

Best Regards,

Rico Zhou

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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