Count uniqe values existing in two variable tables

[Anonymous]

Hi, I have two connected tables.

The first one (Table1) cointains sales data with unique client ID and date among other things. The second one (Table2) is a simple table with dates.

I'm trying add a new column to the second table, where for each date there is a percentage of returning clients from the previous month. Here is the code:

ReturningCustomers=

Var startDate = /// (logic counting beginning of the last month)

Var endDate = ///  (logic counting end of the last month)

Var lastMonth = CALCULATE( DISTINCTCOUNT(Table1[ClinetID]); FILTER( Table1; [Date]>=startD && [Date]<=endD ) )

Var lastMonthColumn = CALCULATE( DISTINCT(Table1[ClinetID]); FILTER( Table1; [Date]>=startD && [Date]<=endD ) )

Var currMonthColumn = CALCULATE( DISTINCT(Table1[ClinetID]); FILTER( Table1; [Date]=Table2[Date] ) )

Var X = ??? (number of unique values existing in both lastMonthColumn and currMonthColumn)

Return

X/lastMonth

The problem is I don't know how to find number of unique values existing in both tables - variable X?

Sample data

Table1:

ClientID	Date
A	01.01.2020
B	15.01.2020
C	30.01.2020
A	02.02.2020
B	02.02.2020
D	06.02.2020

Table2:

Date

31.01.2020

29.02.2020

The expected result is:

Date	Returning customers
31.01.2020	...
29.02.2020	67%

So 2/3 clients returned

[Anonymous · Accepted solution]

Try this

Countrows(union(table1,table2))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Thanks a lot!

I don't know if you meant Countrows(union(table1,table2)) or Countrows(union(currMonthColumn, lastMonthColumn))

But the second one worked as expected 😁

[Anonymous]

I have given generalized answer so have to replace it by using existing table or virtual tables which you stored in visual.

You replaced it correctly.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.