Analysis on new customers compared to previous years

[DS_mho]

Hello,

I problem I hope you can help me with, but first some context.

I have an assignment to find out how many new customers my company had in 2016 compared to the previous 5 year – or in other words how many customer numbers carry out a transaction in 2016 which have not bought anything/haven’t had a transaction with us the previous 5 years. So if a customer bought something 6 years ago, it would be considered a new customer.
As of now this is a manual process in Excel.

But we are only interested in a specific part of the transactions:

• We are only interested in domestic customers (in this case customers from Denmark)
• We only want certain itemgroups
• We only want transaction which have a certain status, for instance delivered or billed
• We aren’t interested in transaction with private customers, only companies.

These informations can be found in 4 different tables.

I have retrieved the tables in question into the query editor, but here is where my troubles start .

I am starting out with the table which has the highest detail (seeing as I want to know if every transaction is delivered, on hold, billed etc. I believe its called granularity) and I make 2 copies and call one “2011-2015” and “2016”. Then use the appropriate datecriteria to filter them accordingli.
I have been told I can use the merge function to isolate the customer numbers with transaction in the “2016” table which do not occur in the other “2011-2015” table. But seeing as I need to respect the above mentioned criteria I need to add the information on country, itemgroups etc. but I cant seem to create relationships between the tables in the query editor in order to make use of “Related” and “LookupValue” functions.

I therefore use “Close and apply” and start working in the PowerBI part where I have managed the “Related” and “Lookupvalue” functions to get all the relevant information into the tables “2011-2015” and “2016”. Now I have (I believe) two tables which I can compare/merge, but there is no (as far as I am aware of) merge functionality in this part of Power BI.

What do I do now ? Can I use the multiple filter functions on the tables “2011-2015” and “2016” ?

Am I approaching this totally wrong ?

I am fairly new to the whole PowerBI concept, so any help/explanation would be very much appreciated.

Best Regards

Martin

[CheenuSing · Accepted solution]

Hi Martin,

Please check out the article

http://www.daxpatterns.com/new-and-returning-customers/

Let me know if you run into specfic problems.

Can you also post some sample data and the visual you expect in the OneDrive and post the link.

Cheers

CheenuSing

[DS_mho]

Hello CheenuSing, 

this looks exactly like the thing I need 🙂

I will read it right away and try to apply it to my own dataset. 

Thank you very much for the quick response  

Best regards 

Martin