Compare 2 Table and calculate

[Anonymous]

Hi,

I Have 2 Table,

Table-A

Date	User ID	Type
27-Dec-22	2211	Active
27-Dec-22	3441	Active
07-Dec-22	3441	Active
27-Jan-23	2211	Active
02-Jan-23	1154	Active
02-Jan-23	3211	Active
16-Jan-23	3211	Active
22-Jan-23	5467	Active
12-Feb-23	1233	Active
21-Feb-23	3456	Active
21-Feb-23	3211	Active
16-Feb-23	1154	Active

and

Table-B

Date	User ID	Type
27-Dec-22	1154	Passport
27-Dec-22	3441	Passport
07-Dec-22	3441	Passport
27-Jan-23	1154	Passport
02-Jan-23	1154	Passport
02-Jan-23	3211	Passport
16-Jan-23	3211	Passport
22-Jan-23	5467	Passport
12-Feb-23	1154	Passport
21-Feb-23	3456	Passport
21-Feb-23	3211	Passport
16-Feb-23	3211	Passport

Need to calculate ,

Numerator - Distinct count of User ID in Table A that matches with Prev month of User ID from Table B

Denominator - Prev month Distinct count of user ID from table B

Result need come like below,

Date	Numerator	Denominator	Ret(Num/Den)
Dec
Jan	1	2	0.5
Feb	2	3	0.666666667

@amitchandak 

@Ashish_Mathur 

@Anonymous 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Below is my solution. And also upload my attachment for your reference.

1.Create a calendar table.

Table = CALENDAR(DATE(2022,12,1),DATE(2023,3,1))

2.Create the yearmonth column both in two tables.

YearMonth = FORMAT([Date],"YYYY-MMM")

3.Create measures. The first measure is to obtain the User ID in Table B for the previous month that matches the current month in Table A.

Measure = 
var _start=EOMONTH(MAX('Table-A'[Date]),-2)+1
var _end=EOMONTH(MAX('Table-A'[Date]),-1)
return
CALCULATE(MAX('Table-B'[User ID]),FILTER(ALL('Table-B'),[User ID]=MAX('Table-A'[User ID])&&[Date]>=_start&&[Date]<=_end))

Numerator = CALCULATE(DISTINCTCOUNT('Table-A'[User ID]),FILTER(ALLSELECTED('Table-A'),[YearMonth]=MAX('Table-A'[YearMonth])&&[User ID]=[Measure]))

Denominator = 
var _start=EOMONTH(MAX('Table-A'[Date]),-2)+1
var _end=EOMONTH(MAX('Table-A'[Date]),-1)
return
CALCULATE(DISTINCTCOUNT('Table-B'[User ID]),FILTER(ALL('Table-B'),[Date]>=_start&&[Date]<=_end))

Ret(Num/Den) = DIVIDE([Numerator],[Denominator])

4.Here's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[MohammadLoran25]

Hi @Anonymous ,

1-In your Table-A Create a calculated column:

YearMOnthIndex = YEAR('Table-A'[Date])*12+MONTH('Table-A'[Date])

2-In your Table-B Create a similar calculated column:

YearMOnthIndex = YEAR('Table-B'[Date])*12+MONTH('Table-B'[Date])

3-Create a measure:

Numerator =
CALCULATE (
    DISTINCTCOUNT ( 'Table-A'[User ID] ),
    FILTER (
        CROSSJOIN ( 'TABLE-A', 'Table-B' ),
        'Table-A'[YearMOnthIndex] = 'Table-B'[YearMOnthIndex] + 1
            && 'Table-A'[User ID] = 'Table-B'[User ID]
    )
)

4-Create a measure: 

Denominatorr = DISTINCTCOUNT('Table-B'[User ID])

Then you can have their proportion as well.

If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

[Anonymous]

Hi Loran,

Thanks for Replying,

This is not working. Instead showing blank value.

Can you try with the sample data which i mentioned above and share me pbix file,if possible.

Result needs to come like above which i mentioned.

Thanks,

@MohammadLoran25 

[MohammadLoran25]

@Anonymous 

Yes I tried it based on your sample data and it worked.