Forum Discussion

Daniel_G's avatar
Daniel_G
Frequent Visitor
5 years ago
Solved

How to calculate average days difference

Hi All,

 

I need to calculate invoice payment days. I managed to do it with a simple DAX formula below and place measure in a table, so that it correctly shows difference in days per document:

Payment days = IF(ISBLANK(MAX('Purchase Payments'[PaymentDate])),DATEDIFF(MIN('Purchase Invoices'[DocumentDate]),UTCTODAY(),DAY),DATEDIFF(MIN('Purchase Invoices'[DocumentDate]),MAX('Purchase Payments'[PaymentDate]),DAY))
 
Datasets are basically like below:
Purchase invoice: Document No, Document Date, Item No, Amount, Vendor Code etc
Purchase Payments: Payment Doc No, Paid Document No (linked with Document No. above as 1:* relationship), Payment Date, Payment Amount, etc
 
The problem I have is that I also want to show it as an average at overall level and vendor level. Unfortunately above formula only works right when calculated for each invoice. If it's used against entire dataset then it shows number of days between first invoice and last payment in the dataset, which is not what I need.
 
I'm thinking about creating a calculated table with list of all invoices in one column and above measure in the other, then calculate average from that table, but I'm not sure how to do it.
 
Please help.
 
Thank you in advance,
Daniel
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    // Obiviously, the field Paid Document No in
// Purchase Payments must be hidden. Slicing
// should always be done via dimensions, never
// fact tables with the sole exception of
// degenerate dimensions. If you stick to this,
// the measure should always work correctly.

Payment days =
AVERAGEX(
    DISTINCT( 'Purchase Invoices'[Document No] ),
    CALCULATE(
        var MaxPaymentDate = 
            MAX( 'Purchase Payments'[Payment Date] )
        var DocumentDate = 
            selectedvalue( 'Purchase Invoices'[Document Date] )
        var DateDiff_ =
            datediff(
                DocumentDate,
                coalesce( MaxPaymentDate, UTCTODAY() ),
                DAY
            )
        return
            DateDiff_
    )
)

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Daniel_G , You need have common document table and then try like

     


    averageX(values(document[document]), IF(ISBLANK(MAX('Purchase Payments'[PaymentDate])),DATEDIFF(MIN('Purchase Invoices'[DocumentDate]),UTCTODAY(),DAY),DATEDIFF(MIN('Purchase Invoices'[DocumentDate]),MAX('Purchase Payments'[PaymentDate]),DAY)))

    • Daniel_G's avatar
      Daniel_G
      Frequent Visitor
      5 years ago

      Thanks, I now know that AverageX was the answer. I think that Anonymous's solution is better, as it does not require creating a 'middle man' table.

       

      Still appreciate your help! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    // Obiviously, the field Paid Document No in
// Purchase Payments must be hidden. Slicing
// should always be done via dimensions, never
// fact tables with the sole exception of
// degenerate dimensions. If you stick to this,
// the measure should always work correctly.

Payment days =
AVERAGEX(
    DISTINCT( 'Purchase Invoices'[Document No] ),
    CALCULATE(
        var MaxPaymentDate = 
            MAX( 'Purchase Payments'[Payment Date] )
        var DocumentDate = 
            selectedvalue( 'Purchase Invoices'[Document Date] )
        var DateDiff_ =
            datediff(
                DocumentDate,
                coalesce( MaxPaymentDate, UTCTODAY() ),
                DAY
            )
        return
            DateDiff_
    )
)

     

    • Daniel_G's avatar
      Daniel_G
      Frequent Visitor
      5 years ago

      Apologies for late reply; I was on sick leave.

      That is actually working. Love the Coalesce formula ğŸ™‚

      The only thing that is missing is Day in DateDiff formula, but that was easy fix ğŸ˜‰

       

      Thanks a lot!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi there. Yeah... I've fixed it. Sometimes I forget to fill in some details because about 95% of all the formulas I write on the forums I do without any model before my eyes and therefore have no means of testing. A quick test would immediately tell me "DAY" was missing. Glad it works for you.