Searching a Previous date

[GuidoPinares]

Hi community,

This probably is a very easy problem but I am strugling with it, I hope you can help me.

Basically I have two different Tables:

Sales(CustomerID, DateSold)
Activities(CustomerID, ActivityDate)

I would like to find the First DateSold by CustomerID and store it in a Column as True or False and finally but most important look if that customer has an Activity prior to the DateSold, a good example is as follows:

Sales Data:

Customer ID	Date Sold
A001	1/1/2023
A002	1/2/2023
A001	1/15/2023
A003	1/25/2023
A004	2/1/2023
A004	2/2/2023

Activities Data

Customer ID	Activity Date
A001	1/1/2023
A002	1/1/2023
A003	1/15/2023
A004	2/2/2023
A004	2/3/2023
A001	3/3/2023

The result should be in two calculate columns as follows:

Customer ID	Date Sold	First Purchase?	Activities Before
A001	1/1/2023	TRUE	FALSE
A002	1/2/2023	TRUE	TRUE
A001	1/15/2023	FALSE	-
A003	1/25/2023	TRUE	TRUE
A004	2/1/2023	TRUE	FALSE
A004	2/2/2023	FALSE	-

The table Sales and Activities, both are able to have the same customerID more than once. We should focus if there is an activity prior to the First Time purchase of the customerID.

Thanks in advance for any suggestion or help!

[GuidoPinares]

Thanks so much @amitchandak, I think other approach for the ACtivities before could be

Activities Before=

var _cnt2 = COUNTROWS(FILTER(Activities, Sales[PrescriberNPI] = Activities[new_cnpi] && Activities[Date] <= Sales[DateSold] && Activities[Date] >= (Sales[DateSold]-90)))

return

if(_cnt2>0, TRUE(), FALSE())

[amitchandak · Accepted solution]

@GuidoPinares , NEw column

Activities Before= var _cnt = countx(Activies, Sales[Customer ID]=Activies[Customer ID] && Activies[Activity Date] <= Sales[Date Sold]) ,  Activies[Customer ID] )

return

if(isblank(_cnt), false(), true ())

First Purchase = var _date = minx(filter(Sales, Sales[Customer ID]= earlier(Sales[Customer ID])), Sales[Date Sold])
return
if(Sales[Date Sold] =_date, TRUE(), FALSE())