<!-- https://learnsql.cpetoday.com/functions/dax/datediff -->
# DATEDIFF

Language: DAX
Category: Date and time
Syntax: DATEDIFF(<date1>, <date2>, <interval>) where interval is SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR
Summary: Returns the number of intervals between two dates.
Returns: A whole number.
Level: Intermediate
Related: YEARFRAC, EDATE, DAY, DATE, AVERAGEX, DIVIDE

## 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
- date1: the earlier or starting date. Required.
- date2: the later or ending date. Required.
- interval: the unit to count in. Use DAY for days. Also available: SECOND, MINUTE, HOUR, WEEK, MONTH, QUARTER and YEAR.

## 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.

```dax
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.

```dax
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
- The order matters. DATEDIFF(date1, date2, DAY) returns date2 minus date1. Put the earlier date first to get a positive result.
- Blank dates break the count. Filter out unpaid invoices before you average, as the example does.
- For MONTH, QUARTER and YEAR, check the result on a few known date pairs before you rely on it. For exact fractions of a year, use YEARFRAC.
- DATEDIFF counts intervals between two dates. It does not count business days. Use a workday flag for that.

## 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.
