custom slicer options

[RKK]

Hi,

I’m trying to create a custom slicer with options for "Last Week," "Last 2 Weeks," and "Last 4 Weeks." To achieve this, I've added separate columns in my Date table for each time period and created another column to categorize these weeks.

However, when I select the "Last 2 Weeks" option, the filter only shows data from the week before last, not including the most recent last week. This issue also happens with the "Last 4 Weeks" option, where the data isn't filtering correctly because it's overlapping with the "Last Week" and "Last 2 Weeks" options.

I’ve tried using calculation groups and field parameters, but I’m still facing this issue.

Can anyone help me resolve this?

[Anonymous · Accepted solution]

Hi @RKK ,

The relative date filter in the slicer will include all dates of the weeks you entered. However, it is related to Today.

If you want to use the date in your own date table, please follow the steps below:

1. Create a new table:

2. Creat the measure:

MaxDate = 
VAR __max_date = CALCULATE( MAX(Tabelle1[Date]),ALL())
VAR __cur_selected = SELECTEDVALUE('Table'[Week Num])
VAR __cur_date = SELECTEDVALUE('Tabelle1'[Date])
VAR __start_date_last_week = __max_date - 6
VAR __start_date_last_2_weeks = __max_date - 13
VAR __start_date_last_3_weeks = __max_date - 20
VAR __start_date_last_4_weeks = __max_date - 27
VAR __tag = 
SWITCH(
    __cur_selected,
    "Last Week", IF( __cur_date>=__start_date_last_week && __cur_date<=__max_date, 1),
    "Last 2 Weeks", IF(__cur_date >= __start_date_last_2_weeks && __cur_date <= __max_date, 1),
    "Last 3 Weeks", IF(__cur_date >= __start_date_last_3_weeks && __cur_date <= __max_date, 1),
    "Last 4 Weeks", IF(__cur_date >= __start_date_last_4_weeks && __cur_date <= __max_date, 1),
    BLANK(), 1
)

RETURN
    __tag

3. Create the visual‘s filter:

4. The result:

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pborah]

@RKK wht not just use a relative date filter in the filters pane instead? Saves you the trouble of creating a separate date table or calculated columns.