Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
Create a week offset column in your date table and filter slicer to 0 as current week
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.
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
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
Thanks! 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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |