Dynamic Slicer to select previous week as default

[tmhalila]

Hello team,

I need your help I have a dataset that tracks daily events with report date and that date is in relationship with the dim_date attached to this post. I want to add a column in the dim_date that I can use in the slicer to select the previous week as the default with the assumption that the week starts on Monday as per the last column of the dim_date.

sample dataset 

[lbendlin · Accepted solution]

That is possible but only if your Calendar table is sourced in Import Mode and is refreshed at least weekly.

Here's the standard pattern (adjust to your needs):

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[Nayan_surya]

Hi @tmhalila ,

My client also had this requirement that he needed to last week by default without any bookmark. What we ended up doing was buying a custom visual named Date Picker by Powerviz. It had this feature of default selection built in so made the job easy and the client happy.

if you can see in the above example the max week is selected by default.

I think you should check them out.

Here is a link if you want to check this visual - https://appsource.microsoft.com/en-us/product/powerbivisuals/truvizinc1674781244292.date-picker-by-p...  (I believe they offer a free version too)

[lbendlin · Accepted solution]

That is possible but only if your Calendar table is sourced in Import Mode and is refreshed at least weekly.

Here's the standard pattern (adjust to your needs):

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

[tmhalila]

Thanks for your idea,

I have updated the changes with data related to my dataset and from the idea you provided I can get the current week but I want the last week before the current week. I have tried some formulas with errors.

Attached is the Power BI project.

Sample Project  

[tmhalila · Accepted solution]

I tried to add this mesure it worked fine!

cx_last_week = 
VAR max_date = MAX(dim_date[cx_end_of_week])
RETURN 
 If(dim_date[cx_end_of_week] = max_date -7, "Last week", dim_date[weekly_start_monday_day_dates])

 Thanks for your time