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.

[Mahmed1]

Hey

thank you once again for all your help, in the measure, is it possible to limit the data to also only go back say 8 weeks

for eg - if i select 20/12/2022 it works beautifully to go back - 7 days

now i can have years worth of data so would be nice to only go back to say 8 weeks worth so charts doenst look

clustered

is this possible?

[AlB]

@Mahmed1 

You could either

1. Place Date in rows and Category in columns, then you will be able to sort by date or

2. Try something like what they explain here:

https://community.powerbi.com/t5/Desktop/Matrix-Column-Head-Order/td-p/71572 

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]

@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.

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_)

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.

[Mahmed1]

hey

had a quick look and it appears to work beautifully so far

Is it possible to sort table based on date selected ie sort by 20-12 so that the category shows Other 1st?

Thank you

[Mahmed1]

Awesome - thank you for taking time out

i will download this file and take see how this approach works 

thank you again

[Mahmed1]

Hey guys - when i put the dates in columns, and categories in rows it doesnt work 😞

The result should be like this (Ive put random numbers in and ranked by the variance column

[AlB]

Can you share the pbix?

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.

[Mahmed1]

how do i add an attachment?

[AlB]

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

[Mahmed1]

Hi - Does this work?

https://www.dropbox.com/s/isn12u1rfkq0igf/Filtered7DayDaxExample.pbix?dl=0

[Mahmed1]

[Mahmed1]

Hi I have used your example pbix file and tried to amend that 

I will send the example file

Ultimately i want to achiythe snapshot excel view

thank you

[Mahmed1]

Thank you once again

i really appreciate all your help - i have an additional metric i would like to add to this matrix please

i have used your solution and in a matrix put

Category in Rows

Dates in Columns

Scores in Values

this gives me a trend of weekly movement based on the selected date

what i would like to add at the end of the matrix is a % change column that compares the selected date against the previous week date and then ranks the whole dataset based on this column

(selected date/prev weekdate) -1

this way i can have a matrix that shows me movements week on week and then the very last column has a % variance for the selected week against previous (ranked highest to lowest)

how would you approach this?

thanks once again

[Mahmed1]

Guys thank u so much

like i say, i am new to this, I didn't even see your attached files 🙂

if i getting it correctly, in my example 

i would create another disconnected calender table ny using min or max of the date table or my tbl_scores table

and then i apply the measure in a filter?

how does the required dates part of the formula work? Does it create a virtual table with a 0 and 1?

[tamerj1]

@Mahmed1 

You can download the file at the bottom of my first reply. 
yes is better to have a new disconnected date table. 
please provide some screenshots 

[Mahmed1]

Thank you

please see my latest response re additional metric

hopefully you can advise the best approach for this

i know i can compare selected date with last week in its own matrix by using dateadd going back 7 days potentially but wanted to add in 1 matrix visual but if this is not the best approach please advise otherwise

thank you again

[Mahmed1]

Thank you i cant wait to try this

my tables already in my file are

Calender

tbl_Scores

tbl_Area

are you saying first duplicate this calender table?

[Mahmed1]

I have 3 tables connected 

Date Table

Scores Table

Area LookUp table

i have standard measures to show me scores for each date and area

do i now create a new disconnected calender table first?

[Mahmed1]

Wow thank you for getting back to me

When you say disconnected date table - do you mean create another date table but have no relationships?

i already have a date table and scores table in my powerbi fil

sorry i am new to this

[tamerj1]

@Mahmed1 

Exactly