Count across two tables based on matching records and date condition

[deedeedudu]

Hi,

I want to calculate conversion as following:

Table AA

Table BB

I want to count the matching records between the two tables with the condition that the latest date of table AA should be <= 15 days of the earliest date in table BB.

As per the above conditions the conversion count should be 1 as following

[amitchandak]

@deedeedudu , In case you need column you add column to table 2

maxx(filter(Table1, Table1[Date] <= Table2[Date] ), Table1[Date])

Count is

Count(filter(Table1, Table1[Date] <= Table2[Date] && Table1[Contract_id] = Table2[Contract_id]), Table1[Date])

[deedeedudu]

Hi,

It is not giving the required output.

[Anonymous]

Hi @deedeedudu,

You can try to use the following measure formula to calculate the row count based on your condition:

formula =
VAR currID =
    MAX ( TableA[ID] )
VAR _dateB =
    MAXX ( FILTER ( TableB, [ID] = currID ), [Date] )
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( TableA ),
            [ID] = currID
                && [Date]
                    <= DATE ( YEAR ( _dateB ), MONTH ( _dateB ), DAY ( _dateB ) - 15 )
        )
    )

Regards,

Xiaoxin Sheng