Track time activity

[GuidoPinares]

Hello Community,

Thanks so much for taking the time to review this, I have 3 tables as follows:

Sales (SaleID, DateOfSale, Customer, Quantity)

Activity (SaleID, DateOfActivity, Customer)

Customer (CustomerID, FullName)

I would like to track the activities that precedes a Sale, this means that someone from the company contact our customer prior to a Sale occurs, in the DB there are records when even though a customer has an activity related they never do a purchase, I want to get just those activities that has a Activity BEFORE the Sale occurs, If theres more than one Activity previous to the sale should be also consider.

For example in the previous image, a sale occurs on 2/4/2023 and also an Activity prior to that Sale on 2/1/2023. I want to consider only those cases, Any activity that occurs prior to a Sale and highlighted in a Matrix just as in the image. 

Thanks for any recommendation or help.

[GuidoPinares]

Thanks so much, the videos you sent help to understand even more the solution.

[amitchandak · Accepted solution]

@GuidoPinares , You can have new column like

New column =

var _cnt = countx(filter(Activity, Activity[Customer] = sales[customer] && Activity[Date] < sales[Date]) , Activity [Customer])

var _cntSalesbefore = countx(filter(sales, sales[Customer] = earlier(sales[customer]) && sales[Date] < earlier(sales[Date]))  , sales[Customer])

return

if(isblank(_cntSalesbefore ), _cnt, blank())

Returning count only when there are no sales before

refer if needed

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s