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
pjporrino
Frequent Visitor

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

Hello,

 

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

 

Captura de pantalla 2021-02-27 a las 20.20.35.png 

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

1 ACCEPTED SOLUTION
nandic
Memorable Member
Memorable Member

Hi,

You can use LookupValue function:

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

 

This is result:

covid.PNG

 

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

Regards,
Nemanja Andic

View solution in original post

2 REPLIES 2
pjporrino
Frequent Visitor

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
Memorable Member
Memorable Member

Hi,

You can use LookupValue function:

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

 

This is result:

covid.PNG

 

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

Regards,
Nemanja Andic

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.

Top Solution Authors