Compare customer activities of 2 companies in last 2 months.

[nutdiablo]

Hi All, 
Hope you are doing well.
I need to compare that is this Customer in Company B also bought with Company A before in the last 2 months.
I can check whenever that customer ever bought with Company A, but I am stuck on last 2 months.
Please help, Thank you.

Company A Table

Date	Customer	Revenue
01-Jan-24	CA	1,000.00
16-Jan-24	CA	2,000.00
01-Feb-24	CA	3,000.00
16-Feb-24	CA	4,000.00
17-Mar-24	CA	5,000.00
20-Apr-24	CA	6,000.00
01-Jan-24	CB	7,000.00
01-Feb-24	CB	8,000.00
16-Jan-24	CC	9,000.00

Company B Table

Date	Customer	Revenue
10-Apr-24	CA	1,000.00
27-Apr-24	CB	2,000.00
30-Apr-24	CC	3,000.00

Result Table

Date	Customer	Revenue	No. Trasaction with Company A in last 2 months	Sum Revenue with Company A in last 2 months
10 April 2024	CA	1,000.00	3	12,000.00
27 April 2024	CB	2,000.00	1	8,000.00
30 April 2024	CC	3,000.00	0	-

[nutdiablo]

It is working great. Thank you very much.

[danextian · Accepted solution]

Hi @nutdiablo ,

Assuming there aren't any dimention tables that link Company A and Company B, the transaction count is the count of rows and the start of month begins on the first day of the month from two months before the current transaction date, try these:

Transactions within the last two months = 
VAR __END_DATE =
    SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
    EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
    CALCULATE (
        COUNTROWS ( TableA ),
        FILTER (
            TableA,
            TableA[Date] >= __START_DATE
                && TableA[Date] <= __END_DATE
                && TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
        )
    )

Revenue within the last two months = 
VAR __END_DATE =
    SELECTEDVALUE ( TableB[Date] )
VAR __START_DATE =
    EOMONTH ( EDATE ( __END_DATE, -3 ), 0 ) + 1
RETURN
    CALCULATE (
        SUM ( TableA[Revenue] ),
        FILTER (
            TableA,
            TableA[Date] >= __START_DATE
                && TableA[Date] <= __END_DATE
                && TableA[Customer] = SELECTEDVALUE ( TableB[Customer] )
        )
    )