Forum Discussion
Post ProdigySelect always the current week within Slicer
I have a simple slicer where every user can select a specific week to review.
Is there any way to automate this slicer in a way that always the current week is selected?
Hi joshua1990
Please correct me if I wrongly understood your issue .
You want to return the value of current week date and then through numbers in the slicer to filter the data . You can use TODAY() and WEEKNUM() to return the latest number .
(1)Create a column to get the week number of the date .
week number = WEEKNUM('Table'[Date])(2)Create a column to judge the date whether belongs to the current week .
current week number = IF(WEEKNUM('Table'[Date])=WEEKNUM(TODAY()),1,0)(3)Add a slicer with the field [week number] and put the [current week number] in slicer visual filters , set the value of the [current week number] to 1 .The current date is 09/14/2021 and week number is 38 ,so the return week number in slicer should be 38 .
(4)Select the number and filter the data you can see a result like this .Then you need to create again a column to determine whether the date is before the current date, in order to return the latest date belonging to the current week .
current date = IF('Table'[Date]<=TODAY(),1,0)Put the [current date] in table visual filters and set it to 1 .The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi joshua1990
Please correct me if I wrongly understood your issue .
You want to return the value of current week date and then through numbers in the slicer to filter the data . You can use TODAY() and WEEKNUM() to return the latest number .
(1)Create a column to get the week number of the date .
week number = WEEKNUM('Table'[Date])(2)Create a column to judge the date whether belongs to the current week .
current week number = IF(WEEKNUM('Table'[Date])=WEEKNUM(TODAY()),1,0)(3)Add a slicer with the field [week number] and put the [current week number] in slicer visual filters , set the value of the [current week number] to 1 .The current date is 09/14/2021 and week number is 38 ,so the return week number in slicer should be 38 .
(4)Select the number and filter the data you can see a result like this .Then you need to create again a column to determine whether the date is before the current date, in order to return the latest date belonging to the current week .
current date = IF('Table'[Date]<=TODAY(),1,0)Put the [current date] in table visual filters and set it to 1 .The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello joshua1990
You can refer to this previous post : https://community.powerbi.com/t5/Desktop/latest-date-in-the-slicer/m-p/890375#M426880
Please accept it as a solution if it solved your issue
joshua1990Thanks! But I don't want to add a Value like "Last week" into the slicer. I want to have numbers in the slicer and always filtered on the latest week.
Hi joshua1990
I am not sure it is possible. I searched for the same functionnality 6 months ago without success. I only find the solution I shared with you.Good luck 🙂
- mussaenda
Community Champion
Hi joshua1990 ,
You can use hasonvalue function to show the latest week if the user selected nothing in slicer.
(this is not the answer that youre looking for, I know. Just to give you an idea)
Hope this helps
- TuesdayApril
Helper I
Create a week offset column in your date table and filter slicer to 0 as current week
