Dax to get Previus 7 days from selected value

[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

 

[Uzma Shaheen]

Try summarize or Calculatetable

 

[jdellasala]

PLEASE use XL2BB.

Book1
	A	B	C	D	E
1	Date	Score		Selected Date	12/20/2022
2	11/29/2022	15		Date	Score
3	11/30/2022	8		12/20/2022	35
4	12/01/2022	11		12/19/2022	10
5	12/02/2022	47		12/18/2022	32
6	12/03/2022	43		12/17/2022	10
7	12/04/2022	8		12/16/2022	21
8	12/05/2022	13		12/15/2022	18
9	12/06/2022	25		12/14/2022	22
10	12/07/2022	29
11	12/08/2022	10
12	12/09/2022	25
13	12/10/2022	17
14	12/11/2022	19
15	12/12/2022	49
16	12/13/2022	22
17	12/14/2022	22
18	12/15/2022	18
19	12/16/2022	21
20	12/17/2022	10
21	12/18/2022	32
22	12/19/2022	10
23	12/20/2022	35
24	12/21/2022	42
25	12/22/2022	16
Sheet1

Cell Formulas
Range	Formula
D1	D1	=PROPER("SELECTED DATE")
A2:A25	A2	=SEQUENCE(24,,DATEVALUE("11/29/2022"))
D3:D9	D3	=SEQUENCE(7,,DATE(YEAR(E1), MONTH(E1), DAY(E1)),-1)
E3:E9	E3	=XLOOKUP(D3#,A2#,B2:B25)
Dynamic array formulas.

The Score column was generated using RANDARRAY and then using Copy/Paste Values, not what you posted.
I showed the last 7 day's values based on the Start Date selected. It was unclear what your goal is. You said "i want to pull back the value from what it was 7 days ago", but then show 12/20/2022 as your "SELECTED DATE" and a list of dates and their values from the table, and some of those dates are more than 7 days earlier than 12/20/2022.

 

[pjmorris]

The data I used is this (slightly different to yours a copying from PDF was a nuisance): (Note that dates are in UK format - dd/mm/yyyy)

Book1
	G	H
1	Date	Score
2	22/12/2022	7
3	21/12/2022	22
4	20/12/2022	15
5	19/12/2022	26
6	18/12/2022	27
7	17/12/2022	27
8	16/12/2022	13
9	15/12/2022	31
10	14/12/2022	32
11	13/12/2022	22
12	12/12/2022	19
13	11/12/2022	45
14	10/12/2022	24
15	09/12/2022	30
16	08/12/2022	42
17	07/12/2022	34
18	06/12/2022	33
19	05/12/2022	33
20	04/12/2022	21
21	03/12/2022	37
22	02/12/2022	10
23	01/12/2022	34
24	30/11/2022	50
25	29/11/2022	19
Table2

I then added a 'Presets' table into which I typed the date I was interested in:

Book1
	C
1	date
2	20-Dec-22
Table2

Which produced this output:

Book1
	K	L
1	Date	Score
2	20/12/2022	15
3	13/12/2022	22
4	06/12/2022	33
5	29/11/2022	19
Table2

The Powerquery code to do this is here: To understand it I suggest you create a blank query, copy the code into it and then follow it step by step. Remember that the small table is named 'tblPresets' and the data table is 'Table5'.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}}),
    Presets = Excel.CurrentWorkbook(){[Name="tblPresets"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Presets,{{"date", type date}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Changed Type1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query",{{"date", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type2",{"date"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each Number.Mod([Date]-[date],7)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Date]-[date]<=0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true) and ([Custom] = 0)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type3",{"Date", "Score"})
in
    #"Removed Other Columns"

HTH