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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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])
)
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

1 REPLY 1
HotChilli
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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