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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Mahmed1
Helper IV
Helper IV

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

@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 

 

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.

 

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.

 

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

Awesome - thank you for taking time out

 

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

 

thank you again

Mahmed1
Helper IV
Helper IV

Mahmed1_0-1671972626482.png

 

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

 

Mahmed1_2-1671973008935.png

 

 

 

 

Can you share the pbix?

 

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.

 

how do i add an attachment?

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_0-1672001695600.png

 

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
Helper IV
Helper IV

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
Helper IV
Helper IV

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?

@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 

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
Helper IV
Helper IV

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
Helper IV
Helper IV

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
Helper IV
Helper IV

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

@Mahmed1 

Exactly

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors