Lookup target from another table per customer(client)

Hallo community members,

I want to create a label which states if business is new or existing business.

Definition:

• All clients with a first purchase date within ‘Today() – 365’ is considered as new business. (client X1 is an example)
• But is there is a gap greater than a year without purchase, than the client is considered a new business again. (see client X3 as example)
• Existing business is considered as ongoing business. First purchase date has to be older than ‘today()-365’ and it must be ongoing business with no greater date difference than 365. (X2 is an example)

Client purchase table

Account table

I need to create a LookUP including an If statement. If the date difference exceeded more than 365 days, than lookup the corresponding purchase date. From that date on the client is considered as new business again for 365 days.

In Excel I created the following measure.

Min if exceeded >365 days = IF(MAX(Date difference >= 365; VLOOKUP(MAX(Date difference); Client purchase table; FALSE; MIN(Purchase date)

New or existing business = IF(Min if exceeded >365 days > (Today() – 365); “New Business”; “Existing Business”

 In PowerBI I created the following custom columns

MinDate = CALCULATE(MIN(Easyflex_data[ndate]); ALL(Easyflex_data); Account[Account name] = EARLIER(Account[Account name]))

MaxDate = CALCULATE(MAX(Easyflex_data[ndate]); ALL(Easyflex_data); Account[Account name] = EARLIER(Account[Account name]))

Min if exceeded >365 days = LOOKUPVALUE(Easyflex_data[ndate]; Easyflex_data[Datediff]; MAX(Easyflex_data[Datediff]); Easyflex_data[RlIdNr]; Account[Registratienummer_Easyflex__c])

But the problem is that the Lookupvalue searches for the greatest date difference and only returns this single date for a single client. I need it to be calculated per client.

I was wondering if you folks could help me out.

Ronald