Downloads
DAXDate and timeIntermediate

DATEDIFF

Returns the number of intervals between two dates.

Syntax
DATEDIFF(<date1>, <date2>, <interval>) where interval is SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR

Returns: A whole number.

What it does

DATEDIFF counts the time between two dates in a unit you choose, most often days. Finance teams use it for days sales outstanding (DSO), for the average number of days customers take to pay, for days past due, and for the number of days in a reporting period. For days, the result is the second date minus the first.

Syntax and arguments

Example

The controller wants DSO for the quarter. DSO is accounts receivable divided by revenue for the period, times the number of days in the period. The number of days comes from DATEDIFF on the first and last dates in the Calendar context.

Days in Period =
DATEDIFF(MIN('Calendar'[Date]), MAX('Calendar'[Date]), DAY) + 1

DSO =
DIVIDE([Accounts Receivable], [Revenue]) * [Days in Period]

January 1 to March 31 is 89 days apart, so the day count is 90 after adding one to include both ends. If receivables are 450,000 and revenue for the quarter is 1,350,000, DSO is 450,000 divided by 1,350,000, times 90, which is 30 days.

Example: a second, different use

Average days to pay, measured invoice by invoice for paid invoices only.

Avg Days to Pay =
AVERAGEX(
    FILTER('Invoices', NOT ISBLANK('Invoices'[PaidDate])),
    DATEDIFF('Invoices'[InvoiceDate], 'Invoices'[PaidDate], DAY)
)

Each invoice gives one number of days, and AVERAGEX takes the mean. A positive value means the customer paid after the invoice date.

Things to watch

In business terms

DATEDIFF is the workhorse for collections. It drives DSO, aging, days to pay by customer, days late on invoices, and the length of the close period. Collections teams watch these numbers every week, and controllers use DSO in the balance sheet review.

Related functions

All DAX functions · View as Markdown · DAX fundamentals