Dax Filtered date to go 7 days back from selected date

[Mahmed1]

Hey Guys, how can i replicate this in powerbi Dax like excel below

Say i had a table like this

Date	Score
29/11/2022​	50​
30/11/2022​	20​
01/12/2022​	7​
02/12/2022​	22​
03/12/2022​	15​
04/12/2022​	26​
05/12/2022​	27​
06/12/2022​	27​
07/12/2022​	13​
08/12/2022​	31​
09/12/2022​	32​
10/12/2022​	22​
11/12/2022​	19​
12/12/2022​	45​
13/12/2022​	24​
14/12/2022​	30​
15/12/2022​	42​
16/12/2022​	34​
17/12/2022​	33​
18/12/2022​	33​
19/12/2022​	21​
20/12/2022​	37​
21/12/2022​	10​
22/12/2022​	34 ​

What i wanted to do is based on whatever date i select - i want to pull back the value from what it was 7 days ago - eg say i selected 20/12/2022

the data table results should look like this

SELECTED DATE	20/12/2022
20/12/2022	37
13/12/2022	24
06/12/2022	27
29/11/2022	50

its just so i can keep a track of trends going back week on week to see what it was 7 days ago or even x amount of days/weeks/months ago

i hope this makes sense

[AlB · Accepted solution]

Hi @Mahmed1 

See it all at work in the attached file.

1. Create a date table DateT
2. Place DateT[Date] in a slicer to select the date
3. Create this measure

ShowMeasure = 
VAR offsetDays_ = 7
VAR diff_ = DATEDIFF ( SELECTEDVALUE ( Table1[Date] ), SELECTEDVALUE ( DateT[Date] ), DAY )
RETURN
    IF ( MOD ( diff_, offsetDays_ ) = 0, 1, 0 )

4. Place Table1[Date] and Table1[Score] in a table visual

5. Apply [ShowMeasure] as filter to the table visual and choose to show items when [ShowMeasure] is 1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @Mahmed1 

See it all at work in the attached file.

1. Create a date table DateT
2. Place DateT[Date] in a slicer to select the date
3. Create this measure

ShowMeasure = 
VAR offsetDays_ = 7
VAR diff_ = DATEDIFF ( SELECTEDVALUE ( Table1[Date] ), SELECTEDVALUE ( DateT[Date] ), DAY )
RETURN
    IF ( MOD ( diff_, offsetDays_ ) = 0, 1, 0 )

4. Place Table1[Date] and Table1[Score] in a table visual

5. Apply [ShowMeasure] as filter to the table visual and choose to show items when [ShowMeasure] is 1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[tamerj1]

Hi @Mahmed1 
You need to have a disconnected date table. Create a filter measure as per below, place it in the filter pane of the table visual, select "is not blank" and apply the filter.

Filter Measure = 
VAR CurrentDate = SELECTEDVALUE ( 'Table'[Date] )
VAR SelectedDate = SELECTEDVALUE ( 'Date'[Date] )
VAR RequiredDates = 
    FILTER ( 
        ALL ( 'Table'[Date] ), 
        'Table'[Date] <= SelectedDate
            && MOD ( SelectedDate - 'Table'[Date], 7 ) = 0
    )
RETURN
    IF ( CurrentDate IN RequiredDates, 1 )