Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

DateNameCostCost3daysago
1/11/2019A100
1/11/2019B200
1/11/2019C300
1/11/2019D400
1/11/2019E500
1/12/2019A600
1/12/2019B700
1/12/2019C800
1/12/2019D900
1/12/2019E1000
1/13/2019A1100
1/13/2019B1200
1/13/2019C1300
1/13/2019D1400
1/13/2019E1500
1/14/2019A16010
1/14/2019B17020
1/14/2019C18030
1/14/2019D19040
1/14/2019E20050

 

Please help

 

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])
)
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

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

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    7 years ago

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