Filter table based on a specific date plus 7 days

Question

I have a table containing a date field (from 1 March 2020 to now) that I need to filter to a specific date and the previous 6 days to give complete week's data. So if I chose 30 March I'd get a table of 24 March to 30 March. If I then chose 31 March the table would show 25 March to 31 March.

I can use a date slicer to choose a range of dates but I want to be able to pick a single date, with Power BI automatically selecting the earlier date.

Any pointers much appreciated.

Mark.

mkRabbani mkRabbani · Accepted Answer · 2020-09-10T16:24:51

You can create two measure - one for Slicer selected date and Another one with 7 day minus from the selected date as below-

Considering your date table name is- Dates

selected_date = SELECTEDVALUE(Dates[Date])

seven_day_starts_from = DATEADD(Dates[Date],-7,DAY)

Now create your calculated measure first like-

total_sales = SUM(Sales[sale])

Here comes how you will always calculate last 7 days sales considering the selected date in the slicer-

7_day_sales = 
(
    CALCULATE(
        [total_sales], 
        DATESBETWEEN(
            'Dates'[Date],
            [seven_day_starts_from],
            [selected_date]
        )
    ) + 0
)

Remember, this is just a sample flow showing how it should work. You should try to follow the steps with your data and table structure. Dates table is a calendar table and Sales table is connected to the Dates table using the Date column.

Filter table based on a specific date plus 7 days

1 Answers