Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hallo community members,
I want to create a label which states if business is new or existing business.
Definition:
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
Solved! Go to Solution.
@Anonymous,
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")
Regards,
Lydia
@Anonymous,
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
Hi @v-yuezhe-msft,
Please find attached a working model in Excel.
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.
@Anonymous,
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
@Anonymous,
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")
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |