cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ronald_01 Regular Visitor
Regular Visitor

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

Client number

Purchase date

Previous purchase date

Date difference

X1

1 January 2017

 

 

X1

12 January 2017

1 January 2017

12

X1

12 August 2017

12 January 2017

213

X1

1 September 2017

12 August 2017

21

X1

6 October 2017

1 September 2017

36

X2

12 February 2015

 

 

X2

1 January 2016

12 February 2015

324

X2

23 December 2016

1 January 2016

358

X2

12 August 2017

23 December 2016

233

X3

2 February 2015

 

 

X3

1 March 2015

2 February 2015

28

X3

5 April 2015

1 March 2015

36

X3

12 September 2015

5 April 2015

161

X3

1 June 2017

12 September 2015

629

X3

6 October 2017

1 June 2017

128

 

Account table

Account

Min purchase date

Max purchase

date

Min if exceeded >365 days

New or existing business

X1

1 January 2017

6 October 2017

 

New Business

X2

12 February 2015

12 August 2017

 

Existing business

X3

2 February 2015

6 October 2017

1 June 2017

New Business

 

 

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Lookup target from another table per customer(client)

@Ronald_01,

Create the following columns in Account table.

Min2 = CALCULATE(MAX(Easyflex_data[Datediff]),ALLEXCEPT(Account,Account[Registratienummer_Easyflex__c]))

MinDate If > 365 days = IF(Account[Min2]>=365, LOOKUPVALUE(Easyflex_data[ndate],Easyflex_data[Datediff],Account[Min2],Easyflex_data[RlIdNr],Account[Registratienummer_Easyflex__c]),Account[MinDate])


New or existing = IF(Account[MinDate If > 365 days]>TODAY()-365,"New Business","Existing Busniess")

1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Ronald_01 Regular Visitor
Regular Visitor

Re: Lookup target from another table per customer(client)

@v-haibl-msft or @v-yulgu-msft do you maybe have a solution for my problem?

 

 

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Lookup target from another table per customer(client)

@Ronald_01,

What are the original columns in the Account table?Based on your formulas, you use different tables and different columns in Excel and Power BI Desktop. Could you please post the tables which you imported into Power BI Desktop here?

Also I get error message when using your VLOOKUP formula in Excel, do you post the complete formula?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ronald_01 Regular Visitor
Regular Visitor

Re: Lookup target from another table per customer(client)

Hi @v-yuezhe-msft,

 

Please find attached a working model in Excel. 

Excel example

 

Original columns in the Account table are the following:

Account[MaxDate]
Account[MinDate]
Account[Account name]
Account[Id]
Account[Registratienummer_Easyflex__c]

Easyflex_data[ndate]
Easyflex_data[Datediff]
Easyflex_data[RlIdNr]

 

There is a connection between the Account and Easyflex_data table based on Account[Registratienummer_Easyflex__c] & Easyflex_data[RlIdNr]

 

Hopefully you are able to help me out. 

Moderator v-yuezhe-msft
Moderator

Re: Lookup target from another table per customer(client)

@Ronald_01,

Could you please share sample data of Account table and Easyflex_data table that you import into Power BI Desktop?

Do you write formulas in Power BI Desktop to calculate MaxDate and MinDate value in Account  table based on the data in Easyflex_data table? Or you simply enter the value for MaxDate and MinDate columns in Account table and then import the data to Power BI?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ronald_01 Regular Visitor
Regular Visitor

Re: Lookup target from another table per customer(client)

Hi @v-yuezhe-msft, Hi Lydia,

 

I have send you a private message. 

 

Greetings,

Ronald

 

Moderator v-yuezhe-msft
Moderator

Re: Lookup target from another table per customer(client)

@Ronald_01,

Create the following columns in Account table.

Min2 = CALCULATE(MAX(Easyflex_data[Datediff]),ALLEXCEPT(Account,Account[Registratienummer_Easyflex__c]))

MinDate If > 365 days = IF(Account[Min2]>=365, LOOKUPVALUE(Easyflex_data[ndate],Easyflex_data[Datediff],Account[Min2],Easyflex_data[RlIdNr],Account[Registratienummer_Easyflex__c]),Account[MinDate])


New or existing = IF(Account[MinDate If > 365 days]>TODAY()-365,"New Business","Existing Busniess")

1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.