Date Range slicer_Period selection buttons

[Anonymous]

Hi All,

I have a requirement sayng that when user selects the period then my week range slicer should show start and end ranges accordingly. So I  have create a dyn_calendar from the existing one as below. You can see my M query.

let
Calendar = Table.SelectColumns(
Calendar,
{"sales_year_week", "weeks_from_now", "ytd_curr_fy"}),
LastWeek = Table.SelectRows(Calendar, each [weeks_from_now] = 1),
Last4Weeks = Table.SelectRows(Calendar, each [weeks_from_now] <= 4),
Last8Weeks = Table.SelectRows(Calendar, each [weeks_from_now] <= 8),
LastYTD = Table.SelectRows(Calendar, each [ytd_curr_fy] = 1),
Custom = Calendar,
Combined =
Table.Combine({
Table.AddColumn(LastWeek, "Type", each "LW"),
Table.AddColumn(Last4Weeks, "Type", each "4W"),
Table.AddColumn(Last8Weeks, "Type", each "8W"),
Table.AddColumn(LastYTD, "Type", each "YTD"),
Table.AddColumn(Custom, "Type", each "Custom")
}),
Result = Table.SelectColumns(Combined, {"sales_year_week", "Type"}),
#"Added Conditional Column" = Table.AddColumn(Result, "Sort1", each if [Type] = "LW" then 1 else if [Type] = "4W" then 2 else if [Type] = "8W" then 3 else if [Type] = "YTD" then 4 else if [Type] = "Custom" then 5 else null)
in
#"Added Conditional Column"

I have given the relationship between my actual calendar and this newly created with bidirectional as all my measures and visuals are based out of my actual calendar. So all my visuals are working as expected when user do some selection.

but when the user is making manual selections then if he tries to select any other period then date range slicer is not getting reset and not showing the correct start and end ranges. My user is expecting it be reset for every period selection he made without clicking on clear selections each time. Is it possible in Power BI? If so please guide me to achieve this.

@pallavi_r  @v-xinc-msft  

[Anonymous]

Hej

Thanks for your response. But unfortunately I am unable to create table using the DAX logic you gave. I am getting the below error
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I have tried other ways of creating table but each time I am getting different error.

[DataNinja777]

Hi @Anonymous ,

The issue you encountered is due to using a DAX measure instead of a calculated table. In Power BI, measures return scalar values, meaning a single number or text value, whereas calculated tables return entire tables. Since you were trying to create a table-like output using a measure, Power BI returned the error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

To fix this, you need to create a calculated table. In Power BI Desktop, go to the "Modeling" tab and click "New Table." Then, paste the following corrected DAX code into the formula bar:

Week Range Slicer =
VAR SelectedPeriod = SELECTEDVALUE('dyn_calendar'[Type])
RETURN
SWITCH(
    TRUE(),
    SelectedPeriod = "Last Week", FILTER('Calendar', [weeks_from_now] <= 1),
    SelectedPeriod = "Last 4 Weeks", FILTER('Calendar', [weeks_from_now] <= 4),
    SelectedPeriod = "Last 8 Weeks", FILTER('Calendar', [weeks_from_now] <= 8),
    SelectedPeriod = "Year-to-Date", FILTER('Calendar', [ytd_curr_fy] = 1),
    'Calendar'
)

Ensure that your "dyn_calendar" table and "Calendar" table are already in your model. Verify that relationships between the tables are correctly set to allow the slicer to interact with the calculated table dynamically.

If you prefer using a measure instead of a calculated table, you can achieve similar functionality by calculating dynamic values. In this case, you can use the following DAX code to create a measure that returns a filtered sum based on the selected period:

Selected Period Filter =
VAR SelectedPeriod = SELECTEDVALUE('dyn_calendar'[Type])
RETURN
SWITCH(
    SelectedPeriod,
    "Last Week", CALCULATE(SUM('Calendar'[Value]), 'Calendar'[weeks_from_now] <= 1),
    "Last 4 Weeks", CALCULATE(SUM('Calendar'[Value]), 'Calendar'[weeks_from_now] <= 4),
    "Last 8 Weeks", CALCULATE(SUM('Calendar'[Value]), 'Calendar'[weeks_from_now] <= 8),
    "Year-to-Date", CALCULATE(SUM('Calendar'[Value]), 'Calendar'[ytd_curr_fy] = 1),
    CALCULATE(SUM('Calendar'[Value]))
)

In summary, use a calculated table when you need to create a new table that dynamically adjusts based on slicer selections. Use a measure when you want to calculate and return dynamic values based on slicer inputs. Both approaches have their use cases, depending on your reporting needs and how you want the slicer interactions to behave in Power BI.

Best regards,

[Anonymous]

This approach also I tried, but unable to create the dynamic calendar based on slicer selections.

[DataNinja777]

Hi @Anonymous ,

To achieve a dynamic slicer behavior where your week range slicer automatically updates based on a selected period slicer, you can create a dynamic calendar table and control the slicer interaction using M Query, DAX measures, and slicer configurations in Power BI.

Start by creating the dynamic calendar (dyn_calendar) in Power Query (M):

let
    Calendar = Table.SelectColumns(Calendar, {"sales_year_week", "weeks_from_now", "ytd_curr_fy"}),
    CustomPeriods = Table.FromRows(
        {
            {"Last Week", 1, "LW"},
            {"Last 4 Weeks", 4, "4W"},
            {"Last 8 Weeks", 8, "8W"},
            {"Year-to-Date", null, "YTD"},
            {"Custom Range", null, "Custom"}
        },
        {"Period Name", "Weeks", "Type"}
    ),
    Combined = Table.Combine({
        Table.AddColumn(Table.SelectRows(Calendar, each [weeks_from_now] <= 1), "Type", each "LW"),
        Table.AddColumn(Table.SelectRows(Calendar, each [weeks_from_now] <= 4), "Type", each "4W"),
        Table.AddColumn(Table.SelectRows(Calendar, each [weeks_from_now] <= 8), "Type", each "8W"),
        Table.AddColumn(Table.SelectRows(Calendar, each [ytd_curr_fy] = 1), "Type", each "YTD"),
        Table.AddColumn(Calendar, "Type", each "Custom")
    }),
    Result = Table.Join(Combined, "Type", CustomPeriods, "Type"),
    #"Added Conditional Column" = Table.AddColumn(Result, "Sort1", each if [Type] = "LW" then 1 else if [Type] = "4W" then 2 else if [Type] = "8W" then 3 else if [Type] = "YTD" then 4 else 5)
in
    #"Added Conditional Column"

After creating the dynamic calendar, you need to control the slicer options using a DAX measure that reflects the user’s selection in the period slicer. Create the following measure in DAX: 

Selected Period =
VAR SelectedPeriod = SELECTEDVALUE('dyn_calendar'[Type])
RETURN
    SWITCH(
        SelectedPeriod,
        "LW", "Last Week",
        "4W", "Last 4 Weeks",
        "8W", "Last 8 Weeks",
        "YTD", "Year-to-Date",
        "Custom", "Custom Range",
        "All"
    )

Next, create a dynamic table to filter the week range slicer based on the selected period. Use the following DAX calculated table to achieve this:

Week Range Slicer =
VAR SelectedPeriod = [Selected Period]
RETURN
    SWITCH(
        TRUE(),
        SelectedPeriod = "Last Week", FILTER('Calendar', [weeks_from_now] <= 1),
        SelectedPeriod = "Last 4 Weeks", FILTER('Calendar', [weeks_from_now] <= 4),
        SelectedPeriod = "Last 8 Weeks", FILTER('Calendar', [weeks_from_now] <= 8),
        SelectedPeriod = "Year-to-Date", FILTER('Calendar', [ytd_curr_fy] = 1),
        FILTER('Calendar', TRUE())
    )

Ensure that your relationships between the tables are correctly set, and use bidirectional filtering between your Calendar and dyn_calendar tables.

To make sure the slicer resets automatically when a user changes the period selection, configure Sync Slicers in Power BI. Go to the View tab in Power BI Desktop, enable Sync Slicers, and link the period slicer and week range slicer across all pages. Alternatively, you can create Bookmarks to reset slicers and assign a button action to reset the slicer selections.

With this setup, your week range slicer will dynamically update to show the correct start and end ranges based on the user’s selected period. If manual slicer selections are made, they will be reset automatically when the period selection changes.

This solution leverages Power Query transformations, DAX measures, and Power BI slicer configurations to achieve a dynamic slicer behavior that responds to user input without manual intervention.

Best regards,