Calculate difference between two dates in different tables not directly related

[Francisco_G_Cal]

Hi

I need to calculate the time elapsed between to dates, in days. The values are the creation date and the payment date for every invoice in my model table. I will use this value to classify my invoices: sort by time passed, classyfy them, make a Pareto Chart... etc. So I need to create a calculated column in the inoice model table (can't imagine an approach using measures). 

But I have two handicaps:

1. Tables ARE NOT DIRECTLY RELATED, so I can't use the RELATED function
2. The invoice can be paid in several installments

So, for each invoice, I need to calculate de time elapsed, in days, between the generation date (in a table) and the MAX payment date of all it's installments (in another indirectly related table)

This:

DaysElapsedInPayment = DATEDIFF(Invoices[InvoiceDate], Max(RELATED(Installments[InstallmentPaymentDate])), DAY)

would work if the tables were directly related an DateDiff whould accept MAX, but no, it's not the case. For your information, if could be usefull to you, this is my invoice-payment model scheme:

Could you help me, please? 

[johnt75 · Accepted solution]

You could try and create a calculated column on Invoices like

Days elapsed in payment =
VAR invoiceNo = Invoices[Invoice Number]
VAR invoiceDate = Invoices[Invoice Date]
VAR maxPaymentDate =
    CALCULATE (
        MAX ( Installments[InstallmentPaymentDate] ),
        Payments[Invoice Number] = invoiceNo
    )
RETURN
    INT ( maxPaymentDate - invoiceDate )

[Francisco_G_Cal]

Thanks! 

I have reached my own solution but it was much more complex:

Var PaymentIDs =
CALCULATETABLE (
    VALUES ( InvoicesInPayment[PaymentID] ),
    FILTER ( InvoicesInPayment, InvoicesInPayment[Invoice Number] = Invoices[Invoice Number] )
)
VARmaxPaymentDate =
CALCULATE (
    MAXX ( Payments, Payments[PaymentDate] ),
    FILTER ( Payments, Payments[PaymentID] IN PaymentIDs )
)

I first got the PaymentID's in a table that later used to filter the Payments table, and iterated its rows with MAXX to get the max date. And similar process for next step, the installments, if there where some. Didn't know it was completely unnecesary as the tables are related (indirectly related, yes, but anyway related), and so are automatically filtered for each row in calculation (not need for me to do so).

Your solution is clean, faster and more effective 💪👌