topic Re: Calculate the difference between values of a column based on a number of rows in DAX Commands and Tips

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

pjporrino — Sat, 27 Feb 2021 19:38:51 GMT

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

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

nandic — Sat, 27 Feb 2021 23:34:09 GMT

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

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

pjporrino — Mon, 01 Mar 2021 13:30:36 GMT

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