Calculate the difference between values of a column based on a number of rows

[pjporrino]

Hello,

I'd really appreciate you can help me. I have a table like this one:

 

Basically, it's showing the number of cases by day and country. The list has many rows. I need to add a column that shows the difference between the number of cases of that day and the number of cases 14 days before. I'm not familiar with DAX, and I know neither how to refer to the specific value of a cell in a column nor how to specify the number of rows behind to look for the difference's value. 

I really appreciate any help you can provide.

Best

Pedro

[pjporrino]

Thank you Nandic, your code helped me to create a column with the number of cases as of 14 days before; I just created another column with the subtraction to get the difference in the number of cases.

Thanks a lot,

Pedro

[nandic · Accepted solution]

Hi,

You can use LookupValue function:

Country_Date - 14 days =
LOOKUPVALUE (
    Covid[New_Cases],
    Covid[Country], Covid[Country],
    Covid[Date], DATEADD ( Covid[Date], -14, DAY )
)

This is result:

Firstly it searches for same country and then for date and date - 14 days.

Regards,
Nemanja Andic