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

[elmurat]

Hello,

Please see the attached pbix for dummy data.

I need to create a dashboard, where the user selects one date from the date slicer (let's say 11/25/2024), and the table visual will show these 4 dates:

1. Selected date (11/25/2024)

2. 7 days ago (11/18/2024)

3. 14 days ago (11/11/2024)

4. 21 days ago (11/04/2024)

[Bibiano_Geraldo]

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

[elmurat]

Thank you, @Bibiano_Geraldo, for your help. I tried to recreate your solution, but when I put the Sales into the matrix, the dates other than the selected ones are getting removed. 

[Bibiano_Geraldo]

Hi, 

Please share no sensitive sample file

[elmurat]

Hi Bibiano, here is the link to the test pbix file:  TestPBIX.pbix. Let me know if you can't access the file.

[Bibiano_Geraldo]

Hi @elmurat ,

To achieve desired result asked in the file, please create the following measure:

For Diference:

Diference = 
VAR totalWeek = 
    CALCULATE(
        SUM(Weekly[Count]),
        FILTER(
            Weekly,
            Weekly[4 Weeks Filter] = 1
        )
    )

VAR vDate = SELECTEDVALUE(Calendar_Table[Date]) - 28

VAR totalMonth = 
    CALCULATE(
        SUM(Monthly[Count]),
        FILTER(
            Monthly,
            Monthly[Date] = vDate
        )
    )

RETURN
    totalWeek - totalMonth

For Diference %

Diference % = 
VAR totalWeek = 
    CALCULATE(
        AVERAGE(Weekly[Percent]),
        FILTER(
            Weekly,
            Weekly[4 Weeks Filter] = 1
        )
    )

VAR vDate = SELECTEDVALUE(Calendar_Table[Date]) - 28

VAR totalMonth = 
    CALCULATE(
        AVERAGE(Monthly[Percent]),
        FILTER(
            Monthly,
            Monthly[Date] = vDate
        )
    )

RETURN
    totalWeek - totalMonth

Your output will look like this:

[v-mengmli-msft · Accepted solution]

Hi @elmurat ,

According to your description, you could try this method.

1. Generate a calendar table based on the maximum and minimum dates of the Date column.

CalendarTable = 
VAR MinDate = MINX(ALL('Table'),'Table'[Date])
VAR MaxDate = MAXX(ALL('Table'),'Table'[Date])
RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Quarter", QUARTER([Date]),
    "Weekday", WEEKDAY([Date])
)

2. Create a DAX measure to generate a virtual table with an interval of seven days, and determine whether the date of the date column is in the virtual table.

Tag = 
VAR SelectedDate = SELECTEDVALUE('CalendarTable'[Date]) 
VAR DateTable =
ADDCOLUMNS(
    GENERATESERIES(0, 3, 1),
    "Date", SelectedDate - [Value] * 7
)
RETURN IF(ISBLANK(SelectedDate),1,if(CONTAINS(DateTable,[Date],MAX('Table'[Date])),1,0))

3. Set Filters.

Best regards,

Mengmeng Li

[elmurat]

hi @v-mengmli-msft , can you please help me build the next part of my calculation?

The next thing I need to build is the Difference from the Monthly number.

Please see attached test pbix file:  TestPBIX.pbix

[elmurat]

thank you very much! This worked!

[elmurat]

@rajendraongole1 @Ritaf1983 @danextian @Bibiano_Geraldo @Jai-Rathinavel  can you help?

[elmurat]

The table will look like this

[Ashish_Mathur]

Hi,

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Calendar Table to the Date column of the Calendar Table
3. To your visual, drag the Date column from the Calendar Table
4. Write these measures

Total sales = sum(Data[Sales])

Total sales 7 days ago = calculate([Total sales],datesbetween(calendar[date],min(calendar[date])-6,min(calendar[date])))

Hope this helps.