Forum Discussion
Daniel_G
5 years agoFrequent Visitor
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...
- Anonymous5 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_ ) )
amitchandak
5 years agoSuper User
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)))