Date Slicer to show last 7 days (excluding today)

[sjpathak]

Hi,

Am aware that slicers cannot have 'visual filters' on them. I have a Dite dimension listing dates for several years, but i want to display a report based on a slicer for last 7 days only (excluding today). I have hacked it to work to show 7 days only by adding two slicers, selecting last week in the first and then hiding it.

Date Slicer

However, if TODAY() = 27th Sept 2018, I don't want that to be shown in the slicer, but dates for the week before today.

Questions

1. Is it possible to have a date slicer working on a date dimension to display only last 7 days (excluding today)?

2. Is it possible to apply a visual level filter on Date Dimension when its used as a slicer?

Thanks

[v-piga-msft]

Hi @sjpathak,

---

 Questions

1. Is it possible to have a date slicer working on a date dimension to display only last 7 days (excluding today)?

2. Is it possible to apply a visual level filter on Date Dimension when its used as a slicer?

---

We may not create a date slicer working on a date dimension to display only last 7 days (excluding today).

Why not use Relative date filter in visual level filter?

2. Date slicer doesn't have visual level filter in Power BI.

You could vote this similar idea in Power BI ideas forum.

Best Regards,

Cherry

[sjpathak · Accepted solution]

Well, this eventually worked for me: https://prologika.com/overwriting-power-bi-filters/!!

[PattemManohar]

Try adding another field to flag Last7Days as below

IF([Date]>=NOW()-7 && [Date]<=NOW()-1,"Y","N")

Add the flag to report level filters to "Y"

Hope this resolves your issue !!

[sjpathak]

I was doing that only initially, but the issue of adding report/page level filters is that then all my visuals get filtered on that, however, I want one of my visual to not to filter on dates, but show all available dates.

That's why I had to remove the report/page level filter and started adding that IsLastWeek kind of filter to individual visuals. But removing the report level filter altered my Date slicer and that's why i hacked it by adding a duplicate hidden slicer. However, I need the last day not shown in that, and that the current problem.

Maybe am following the wrong chain of thoughts to solve a totally different problem of ignoring report level filters for a single visual, but this is the best I could work it out. Any inputs on how to achieve to remove the report level filter to a single visual or to apply visual filter to a slicer would help immensely.

[PattemManohar]

Ok, try creating a calculated table which will contain only the dates which are flagged as "Y" (as mentioned in above post) in the Date table. Use the date field in this new calculated table as the Slicer. Then, you are not using any report or visual level filters... Make sure you have datekey as well to have relationship with the main Date table, otherwise your data will not get filtered out as per the selection in the slicer. Hope this should work !!