title: Calculate difference between two dates in different tables not directly related in PowerBI
body: <p>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).</p>
  <p>But I have two handicaps:</p>
  <ol>
  <li>Tables ARE NOT DIRECTLY RELATED, so I can't use the RELATED function</li>
  <li>The invoice can be paid in several installments</li>
  </ol>
  <p>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)</p>
  <p>This:</p>
  <p><code>DaysElapsedInPayment = DATEDIFF(Invoices[InvoiceDate], Max(RELATED(Installments[InstallmentPaymentDate])), DAY)</code></p>
  <p>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:</p>
  <p><a href="https://i.stack.imgur.com/UEFVq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UEFVq.jpg" alt="enter image description here" /></a></p>
  <p>Could you help me, please?</p>
  <p><strong>EDIT:</strong></p>
  <p>As requested, I explain more deeply my data model...</p>
  <p>My model looks complex because an invoice can be paid by several payments (part in cash, part in direct debit, for example), so a invoice can have MULTIPLE payments. At the same time, a payment can involve several invoices. Thats why my invoices-payments is many-to-many. And also, a payment (for example a payment by direct debit) can be divided in installments that are monthly sent to bank.</p>
  <p>My model has lots of tables, not in english. For my purpose, I mock the needed ones here:</p>
  <p><a href="https://i.stack.imgur.com/0qk68.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0qk68.jpg" alt="enter image description here" /></a></p>
  <p>As you can see in the mocked data:</p>
  <ul>
  <li>One invoice is paid in a single payment. It's the common case. We only have a single payment date in 'Payments'. That one is the one I need.</li>
  <li>Another invoice is paid in TWO payments (part in cash, part payed by VISA), in different days. I need the date of the LAST of both payments.</li>
  <li>Last case, I have two invoices that will be paid as a 'SINGLE' payment. Both should have to return the PaymentDate in Payments. But, at the las moment, we agreed with the client to pay them in installments. So installments are created, each one with a DueDate an the date wuere they were finally payed. So I need to get the LAST date of all installments for this payment.</li>
  </ul>
  <p>It's complex, I know, but at the end, is about:</p>
  <ul>
  <li>Given a value, find related values in anoter table indirectly related</li>
  <li>From all values found, get the greatest</li>
  <li>The function must perform theese operations, and put the results in a calculated column.</li>
  </ul>