Forum Discussion
Compare 2 Table and calculate
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 |
Anonymous
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.
4 Replies
- MohammadLoran25
Solution Sage
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,
LoranHi 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
Anonymous
Yes I tried it based on your sample data and it worked.
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.
