Forum Discussion

Francisco_G_Cal's avatar
Francisco_G_Cal
Helper I
4 years ago
Solved

Calculate difference between two dates in different tables not directly related

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 inv...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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 )
johnt75's avatar
johnt75
Super User
4 years ago

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's avatar
    Francisco_G_Cal
    Helper I
    3 years ago

    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 ğŸ’ªğŸ‘Œ