Caculate customer appointments using contract start and end date from different table.

[Anonymous]

Dear all,
My first post on the forum, so I hope I manage to get my question across.
I'd like to calculate the number of appointments each customer makes within their contract.
I've got two tables T_Appointments and T_Contracts, which are related through a customer table (T_Customer with unique customer numbers, not shown).

T_Appointments
Date_a Customer_id Type_a
15-06-2023 123 call
16-06-2023 123 visit
17-06-2023 402 call
19-06-2023 123 call
15-01-2024 402 call
20-01-2024 683 visit
12-02-2024 123 call
15-03-2024 123 call
06-04-2024 123 visit

T_Contracts (Contract details)
Customer_id Contract_id Contract_start Contract_end
123 125 01-01-2014 01-01-2015
123 357 15-06-2023 15-08-2023
123 915 01-01-2024 15-06-2024
402 471 01-01-2024 15-06-2024
683 120 01-01-2014 01-01-2015
683 472 01-01-2024 01-01-2025

To answer my question I tried to make a measure that would return:
Contract_id Appointment count
125 0
357 3
915 2
471 1
120 0
472 1

Or alternatively, create a new column in table_a that contains the contract_id corresponding to the customer and the date.
Date_a Customer_id Type_a Contract_id
15-06-2023 123 call 357
16-06-2023 123 visit 357
17-06-2023 402 call null

The closest I could get was calculating the number of appointments for the the last contract.
contract_appointment_count = SUMX(SUMMARIZE('T_customer','T_customer'[Customer_id],"_1",COUNTX(FILTER('T_Appointment','T_appointment'[Date_a] >= MAX(T_Contracts[Contract_start]) && 'T_Appointment','T_appointment'[Date_a] <= MAX(T_Contracts[Contract_end])),'Table_a'[customer_id])),[_1])

Thanks!
Roos

[lbendlin · Accepted solution]

Your math is a little off.

Appointments = 
CALCULATE (
    COUNTROWS ( T_Appointments ),
    TREATAS ( VALUES ( T_Contracts[Customer_id] ), T_Appointments[Customer_id] ),
    T_Appointments[Date_a]
        IN CALENDAR (
            MIN ( T_Contracts[Contract_start] ),
            MAX ( T_Contracts[Contract_end] )
        )
)

Note:  You will want to invest in a proper data model including customer dimension and calendar table.