How to calculate average days difference

[Daniel_G]

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 · Accepted solution]

// 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]

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]

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.

[amitchandak]

@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]

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!