How to display dynamic various dates using single slicer selection?

[huongvm2]

Hello, I'm using PowerBI Desktop. 

I have a slicer with dates (from the dim_yearmonth table, which is connected to the posted_dt column in the main data table).  What I want is: when selecting a specific year/month—for example, 2025/03—all visuals should display the following:

• The selected month (March 2025)

• The previous month (February 2025)

• The end of the previous year (December 2024)

In this example, the visuals would show 2025/03, 2025/02, and 2024/12.

Additionally, I would like to display two MoM (Month-over-Month) columns with the corresponding figures in the same matrix.

Can anyone help me with this? I'm okay with creating parameter tables if needed.

Below is the visual I want to display in Power BI, along with the main data table. The RW (%) value was calculated using a measure.

[DataNinja777 · Accepted solution]

Hi @huongvm2 ,

To display multiple time periods from a single slicer selection, you can use a disconnected table to create the matrix columns and DAX measures to populate the values. This approach correctly uses your calendar table for time-based filtering, which is a Power BI best practice.

First, create a new disconnected table that will define the column headers in your visual. You can use "Enter Data" or create a calculated table with the following DAX. Remember to sort the Period column by the ID column.

Dynamic Periods = 
DATATABLE (
    "ID", INTEGER, "Period", STRING,
    {
        {1, "End of Previous Year"},
        {2, "Previous Month"},
        {3, "Selected Month"}
    }
)

Next, create the necessary DAX measures. These measures first identify the target dates from your slicer and then calculate the values for each specific period by removing the slicer's context and applying a new date filter on your calendar table, dim_yearmonth. Below are the measures for RWA; you would create a similar set for EAD.

// --- Helper Measures to find target dates ---
Selected_Date = MAX('dim_yearmonth'[Date])
Target_Date_Selected = EOMONTH([Selected_Date], 0)
Target_Date_Previous = EOMONTH([Selected_Date], -1)
Target_Date_EoPY = EOMONTH(DATE(YEAR([Selected_Date]) - 1, 12, 31), 0)

// --- Switcher measure for the matrix values ---
Dynamic RWA = 
SWITCH(TRUE(),
    SELECTEDVALUE('Dynamic Periods'[Period]) = "Selected Month",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Selected]),

    SELECTEDVALUE('Dynamic Periods'[Period]) = "Previous Month",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Previous]),

    SELECTEDVALUE('Dynamic Periods'[Period]) = "End of Previous Year",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_EoPY])
)

// --- MoM calculation measures ---
RWA Selected Month = CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Selected])
RWA Previous Month = CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Previous])
MoM abs.delta RWA = [RWA Selected Month] - [RWA Previous Month]
MoM %delta RWA = DIVIDE([MoM abs.delta RWA], [RWA Previous Month])

Finally, build your visual. Add a Matrix to your report, place Indicators on Rows, and the Period column from your Dynamic Periods table on Columns. Add your measures (e.g., Dynamic EAD, Dynamic RWA, MoM %delta RWA, MoM abs.delta RWA) to the Values field. In the Format pane, under Values, ensure the "Switch values to rows" toggle is off to place the measures as sub-columns under each period.

Best regards,

[v-pnaroju-msft]

Hi huongvm2,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @DataNinja777 and @danextian for your response.

Hi huongvm2,

We would like to check if the solution provided by @DataNinja777 , @danextian has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[danextian · Accepted solution]

Hi @huongvm2 

You’ll need to use a disconnected table because using a column from a related or the same table in a slicer will limit the rows that are displayed to only those selected. While filter context can affect calculations beyond what’s currently visible, it doesn’t change which rows are actually shown.

Below is a sample measure

Total Revenue in the last 3 months = 
VAR _maxDate =
    MAX ( DisconnectedDate[Date] )
VAR _startOfMonth =
    EOMONTH ( _maxDate, -1 ) + 1
VAR _minDate =
    EDATE ( _startOfMonth, -2 )
RETURN
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS ( Dates[Date] >= _minDate && Dates[Date] <= _maxDate )
    )

Please see the attached pbix.

[DataNinja777 · Accepted solution]

Hi @huongvm2 ,

To display multiple time periods from a single slicer selection, you can use a disconnected table to create the matrix columns and DAX measures to populate the values. This approach correctly uses your calendar table for time-based filtering, which is a Power BI best practice.

First, create a new disconnected table that will define the column headers in your visual. You can use "Enter Data" or create a calculated table with the following DAX. Remember to sort the Period column by the ID column.

Dynamic Periods = 
DATATABLE (
    "ID", INTEGER, "Period", STRING,
    {
        {1, "End of Previous Year"},
        {2, "Previous Month"},
        {3, "Selected Month"}
    }
)

Next, create the necessary DAX measures. These measures first identify the target dates from your slicer and then calculate the values for each specific period by removing the slicer's context and applying a new date filter on your calendar table, dim_yearmonth. Below are the measures for RWA; you would create a similar set for EAD.

// --- Helper Measures to find target dates ---
Selected_Date = MAX('dim_yearmonth'[Date])
Target_Date_Selected = EOMONTH([Selected_Date], 0)
Target_Date_Previous = EOMONTH([Selected_Date], -1)
Target_Date_EoPY = EOMONTH(DATE(YEAR([Selected_Date]) - 1, 12, 31), 0)

// --- Switcher measure for the matrix values ---
Dynamic RWA = 
SWITCH(TRUE(),
    SELECTEDVALUE('Dynamic Periods'[Period]) = "Selected Month",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Selected]),

    SELECTEDVALUE('Dynamic Periods'[Period]) = "Previous Month",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Previous]),

    SELECTEDVALUE('Dynamic Periods'[Period]) = "End of Previous Year",
    CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_EoPY])
)

// --- MoM calculation measures ---
RWA Selected Month = CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Selected])
RWA Previous Month = CALCULATE(SUM('Main Data Table'[RWA]), REMOVEFILTERS('dim_yearmonth'), 'dim_yearmonth'[Date] = [Target_Date_Previous])
MoM abs.delta RWA = [RWA Selected Month] - [RWA Previous Month]
MoM %delta RWA = DIVIDE([MoM abs.delta RWA], [RWA Previous Month])

Finally, build your visual. Add a Matrix to your report, place Indicators on Rows, and the Period column from your Dynamic Periods table on Columns. Add your measures (e.g., Dynamic EAD, Dynamic RWA, MoM %delta RWA, MoM abs.delta RWA) to the Values field. In the Format pane, under Values, ensure the "Switch values to rows" toggle is off to place the measures as sub-columns under each period.

Best regards,