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_ ) )
Daniel_G
5 years agoFrequent Visitor
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
5 years agoNot applicable
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.