Forum Discussion
last 5 weeks filter
Hi,
I would like to apply a filter on my all pages in report to show last 5 weeks data. But when user wants to disable that he could able to see all the weeks of that year.
Is there any possibility that we place a slicer with disable and enable option to see last 5 weeks data other than relative time filter in filter panel?
Thank you in advance:-)
Hi,
I got the solution as,
I have created two columns
Weeks From Today = IFERROR( DATEDIFF('DateTable'[Date];TODAY();WEEK);-5)
and
LAST5WEEKS = IF(DateTable[Weeks From Today] >=0 && DateTable[Weeks From Today] <=5; "LAST 5 Weeks" ; " ALL WEEKS")
Then i used this column in filter. Then in filter panel select only "LAST 5 WEEKS". So, that i can select and deselect the LAST 5 WEEKS.
Used Sync filter to all pages in the report.
Thank you.
6 Replies
Hi, Anonymous
1 First, you can create a Calculated column.
Last5weeks =
var weeknumber=MAX('date'[week])
return
if(
'date'[week]>weeknumber-5,
"last 5 weeks"
)
2 Put this column into a Slicer and select the checkbox of Blank, exclude it from the Slicer. Then check or uncheck “last 5 weeks” will show data of last 5 weeks or all the weeks.
The Slicer will look like this:
3 If you want apply this filter to all pages in report, you can use Sync Slicers to achieve it.
Here is the sample.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , I think a relative date slicer is the best option. I think you can use the bookmark to hide and show this five week slicer.
Refer to these two
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://radacad.com/clear-all-slicers-in-power-bi-a-bookmark-story
Use clear all for clear one
Thank you for the reply amitchandak .
i am not so clear yet.
I have these slicers on my first page of report. User needs to see last 5 calender weeks of data filtered on all reports by default.
Same time they should also able to change that to their selected year and week.
I hope i have explained issue well.
Anonymous ,
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Type = Switch( True(),
[start week] <=Today() -35 && [end date]>=Today() -35,"Last 5 Weeks" ,
[Week Name]
)
You can select Last 5 Weeks, you can unselect. when you need othersor you can use bookmarks, switch options
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
