Making a measure based off of two tables having the same Customer ID on the same date

[Anonymous]

So I work for a retail company that provides appointments and I'm interested in getting a revenue per appointment metric. However, not everyone that buys something came to an appointment. 

I have a transaction history table with Customer ID along with an appointment history table with the same customer ID. Both tables also have a date column. 

What's the best way for me to sum sales of Customer IDs if they have a row in the appointment table on the same date? 

Thanks!

[Rodrigo_Carvalh]

I would use nested sumx filtering the rows, something like:

sumx(AppointmentTable;

   sumx( Filter(SalesTable; sales[id] = appointment[id] && sales[date] = appointment[date]);

      sales[revenue]

   )

)

[Anonymous]

So that looks like a good solution but I have a hiccup. My sales table, appointment table, and customer table are all tied to Locations. 

The sales table contains a 7 digit customer ID while the appointment table has a 15 digit customer ID. The accounts table has both of those.

I tried to make a related() in one of the sales or appointment tables but it would introduce ambiguity to Accounts and Locations. Is there a way for me to loop the sumx through accounts?

Would sales[id]=customer[sales id], customer[appointment id]=appointment[id], appointment[date]=sales[date] work?

[Rodrigo_Carvalh · Accepted solution]

Add another sumx like this:

sumx(customer;

   sumx(Filter(AppointmentTable; customer[id15] = appointment[id15]);

      sumx( Filter(SalesTable; sales[id7] = customer[id7] && sales[date] = appointment[date]);

         sales[revenue]

   )

)