When I select a date, also show previous 3 dates.

Hello, elmurat!

This is an excellent question. I’m going to use a trick to ensure you get exactly the result you want.

Why am I using tricks? Because we have two main reasons for this:

1. When the user selects a date in the slicer, the Matrix is automatically filtered to that specific date, but what you really want is the selected date plus the rows with dates 7, 14, and 21 days prior to the selected date, included in the Matrix.

2. You might think: "Why not use functions like ALL or ALLSELECTED to remove filters?" The issue is that by doing this, the slicer filters will be ignored, which is not what you want. The selected value on the slicer need to be considered for calculations, and those functions do not achieve that.

Now, let's get to work!

Let's assume you're in a scenario like the following:

1. You have a fact table, where the sales data, for example, is stored.
2. You also have a calendar dimension table and marked as date calendar

What do you need to do:

1. Duplicate the calendar table using the following DAX (replace the table name with the correct name of your calendar table):

Calendar2 = 'Calendar' -- Make sure to replace the name with the name of your calendar table

2. Create the measure with the following DAX:

Measure = 
-- Defining the selected date
VAR SelectedDate = 
    CALCULATE(
        MAX('Calendar'[Date]), 
        ALLSELECTED('Calendar'[Date])  -- Gets the selected date in the slicer, considering the slicer filter
    )

-- Defining the dates 7, 14, and 21 days before the selected date
VAR DateMinus7 = SelectedDate - 7
VAR DateMinus14 = SelectedDate - 14
VAR DateMinus21 = SelectedDate - 21

-- Returning 1 if the date in Calendar2 matches the selected date or any of the previous dates, otherwise returning 0
RETURN
IF(
    SELECTEDVALUE('Calendar2'[Date]) = SelectedDate ||
    SELECTEDVALUE('Calendar2'[Date]) = DateMinus7 ||
    SELECTEDVALUE('Calendar2'[Date]) = DateMinus14 ||
    SELECTEDVALUE('Calendar2'[Date]) = DateMinus21,
    1,
    0
)

3. Ensure the two calendar tables are related to the fact table. The relationship is important to ensure the matrix shows the data correctly. Example below:

4. Add a slicer to your report using the date column from the original Calendar table.

5. Add a matrix to your report and add the date column from the Calendar2 table (the duplicated table referencing the original calendar table) to the Rows field of the matrix.

6.In the Filters pane, with the matrix selected, add the measure you created to the Filters on this visual field. In the Show items when the value field, select is 1, and apply the filter.

Now, your table will return the results as expected!

Note: If nothing is selected in the slicer, it will default to considering the last date in your report.

See the result below:

Download the sample here