Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mahmed1
Helper IV
Helper IV

Dax Filtered date to go 7 days back from selected date

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

Say i had a table like this

DateScore
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 DATE20/12/2022
  
  
20/12/202237
13/12/202224
06/12/202227
29/11/202250
 
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
2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

View solution in original post

AlB
Super User
Super User

@Mahmed1 

See it all at work in the attached file (Page 1).

If you place both Category and Date in the rows of a matrix visual it will work.

AlB_1-1672012455618.png

 

With Date in columns I'm afraid the auto-exist behavior will make the current approach fail.

An alternative that will work though is to create another measure that leverages the one we had earlier. It assumes you want the SUM of the scores; if not, you can update it accordingly:

ScoreMeasure = 
VAR aux_ = SUM(Table1[Score])
RETURN
IF([ShowMeasure] = 1, aux_)

 

AlB_2-1672012664828.png

 

SU18_powerbi_badge

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.

 

View solution in original post

22 REPLIES 22
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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
Super User
Super User

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.

2.png1.png

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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors