cancel
Showing results for
Did you mean:
Frequent Visitor

## Find value within the same table 3 days ago using DAX

Below is a sample data that I am working with, I need to find the value in the same table based on Name and date going back 3 days.

 Date Name Cost Cost3daysago 1/11/2019 A 10 0 1/11/2019 B 20 0 1/11/2019 C 30 0 1/11/2019 D 40 0 1/11/2019 E 50 0 1/12/2019 A 60 0 1/12/2019 B 70 0 1/12/2019 C 80 0 1/12/2019 D 90 0 1/12/2019 E 100 0 1/13/2019 A 110 0 1/13/2019 B 120 0 1/13/2019 C 130 0 1/13/2019 D 140 0 1/13/2019 E 150 0 1/14/2019 A 160 10 1/14/2019 B 170 20 1/14/2019 C 180 30 1/14/2019 D 190 40 1/14/2019 E 200 50

This is what I have so far which is giving me the result but it requires me to create a column specific for 3 days. Hope there is much effective way fo doing it so I have more flexibility with respect to how many days I can go back.

Cost3daysago = CALCULATE(
MIN(Table[Cost]),
FILTER(
ALL(Table),
Table[Date] = EARLIER( table[3day].[Date])  -- New column in the table with 3 days ago
&& Table[Name] = EARLIER( Table[Name])
)
1 ACCEPTED SOLUTION
Super User II

You could use a variable to get the date 3 days before

```Cost3daysagoX = VAR _DateBefore = Table[Date] - 3
RETURN
CALCULATE(
SUM(Table[Cost]) + 0,
FILTER(
ALL(Table4),
Table[Date] =  _DateBefore
&& Table[Name] = EARLIER( Table[Name])
)
)```
Super User II

You could use a variable to get the date 3 days before

```Cost3daysagoX = VAR _DateBefore = Table[Date] - 3
RETURN
CALCULATE(
SUM(Table[Cost]) + 0,
FILTER(
ALL(Table4),
Table[Date] =  _DateBefore
&& Table[Name] = EARLIER( Table[Name])
)
)```

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!