topic Re: Dax Filtered date to go 7 days back from selected date in DAX Commands and Tips

Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 14:53:40 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

tamerj1 — Sat, 24 Dec 2022 15:31:55 GMT

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 )

Re: Dax Filtered date to go 7 days back from selected date

AlB — Sat, 24 Dec 2022 15:40:28 GMT

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.

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 15:41:38 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 15:45:08 GMT

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?

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 15:53:42 GMT

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?

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 16:33:33 GMT

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?

Re: Dax Filtered date to go 7 days back from selected date

tamerj1 — Sat, 24 Dec 2022 16:59:21 GMT

Mahmed1

Exactly

Re: Dax Filtered date to go 7 days back from selected date

tamerj1 — Sat, 24 Dec 2022 17:06:44 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 18:17:05 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sat, 24 Dec 2022 19:35:34 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sun, 25 Dec 2022 12:56:54 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

AlB — Sun, 25 Dec 2022 20:15:16 GMT

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.

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sun, 25 Dec 2022 20:30:41 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sun, 25 Dec 2022 20:39:37 GMT

how do i add an attachment?

Re: Dax Filtered date to go 7 days back from selected date

AlB — Sun, 25 Dec 2022 20:40:31 GMT

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sun, 25 Dec 2022 20:49:36 GMT

Hi - Does this work?

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

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Sun, 25 Dec 2022 20:55:01 GMT

Re: Dax Filtered date to go 7 days back from selected date

AlB — Mon, 26 Dec 2022 00:01:28 GMT

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.

Re: Dax Filtered date to go 7 days back from selected date

Mahmed1 — Mon, 26 Dec 2022 01:08:31 GMT

Awesome - thank you for taking time out

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

thank you again