Forum Discussion
Help improve my filter control
I'm currently putting together a dashboard that not only summarises weekly performance but also offers insight on the current financial year, month, etc. I want to create this dashboard in such a way that its displays all the information as if you were viewing it at the end of the actual week. All of my metrics are calculated using measures, and I rely on two different filter slicers to narrow the timeframe.
I use the last day of the selected week as the maximum date, from here I've made measures to go back in time and generate the FYTD, MTD and W-1 values, however I am trying to show a trend of the past 8 weeks (current + 7 previous). When both filters are active I cannot figure out a way to expand the represented values past the current week, when both filters are inactive I cannot find a way to apply a measure to the visual to get the correct date range. Is this possible using measures, as I worry that the measure can only hold a single value at a time. I've attached a screenshot of my page, one trend is fully filtered whereas the otherone is only constrained by the year.
I'm trying to achieve something similar to the bottom visual but with control over the displayed weeks, relative to the slicer week.
I think you can adapt the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/. You may also want to explore using the new calendar functionality so that you can create a weekly calendar, then duplicate that calendar as 'Previous Date'.
4 Replies
I think you can adapt the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/. You may also want to explore using the new calendar functionality so that you can create a weekly calendar, then duplicate that calendar as 'Previous Date'.
Hello,
You can try to ignore the slicer filter inside the measure with something like removing the date filter and then rebuilding the 8-week window relative to the selected week using the max selected date, like with CALCULATE and a date range filter.
Another idea it could be using a disconnected week slicer table and using the selected value only to calculate the anchor week, while the visual uses the real date table.
Best regards,
DanieleHi MilanJov,
Thank you DanieleUgoCopp and johnt75 for your instan tresponses to the query.
This scenario happens because when you select a week in the slicer, Power BI filters the date table to that specific week only. As a result, the visual cannot show the previous weeks because they are removed from the filter context. To solve this, you can treat the selected week only as a reference point (or anchor week) and then dynamically calculate the required range inside your measure. In simple terms, you capture the last date of the selected week and use it to calculate a custom date window such as the current week plus the previous seven weeks. Inside the measure you remove the slicer’s direct filter on the date table and then apply your own date range relative to the selected week. This allows the visual to display the full 8-week trend while still letting the slicer control which week is considered the current one.Thank you
CST Member.HI MilanJov ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you
