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: Pa...
  • 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_
        )
    )