Compare 2 Table and calculate

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.