Two tables(years), same structure, one relation, what function is used in this case?

[Anonymous]

Good morning. I have a problem to solve, if you can help me. I have two tables (different year: 2018-2019) with the same structure. I have two important variables in each of those tables : Clients and year. I want to identify two groups :
The clients that come again in 2019 and classify them as continuators
Customers who do not come in 2019 : classify them as lost.
Any idea how? Thank you very much.

[rainer1 · Accepted solution]

Hi,

It was quite fun to solve your Problem. 

At First two sample Tables based on your Information.

The Data Inside of the Tables.

Then I build up a new calculated Table I call it Clients. With all Unique Clients as the first Column with this Dax Code.

Clients = DISTINCT(UNION(ALLSELECTED('Client-Year2018'[Client]);ALLSELECTED('Client-Year2019'[Client])))

After that you can calculate for each unique Client if it present or not with the Dax Code below in a new Calculated column.

I Modified it a bit to know if a customer comes new in 2019.

Classification = 

var present2018 = LOOKUPVALUE('Client-Year2018'[Client]; 'Client-Year2018'[Client]; Clients[Client])
var present2019 = LOOKUPVALUE('Client-Year2019'[Client]; 'Client-Year2019'[Client]; Clients[Client])
Return
IF(present2018 = Clients[Client] && present2019 = Clients[Client]; "continuators"; IF(present2018 = Clients[Client] && NOT(present2019 = Clients[Client]); "Lost"; IF(NOT(present2018 = Clients[Client]) && present2019 = Clients[Client]; "New")))

The new Table looks like this.

I hope this will help.

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[Anonymous]

Thank you. Very thankful