DATEDIFF
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the DATEDIFF Function?
The DATEDIFF function is a DAX function (Data Analysis Expressions) that calculates the difference between two dates. It returns the number of intervals (days, months, years, etc.) between two given dates.
Syntax of the DATEDIFF Function
The syntax of the DATEDIFF function is as follows:
DATEDIFF(❰interval❱,❰startdate❱,❰enddate❱)
- **interval**: The type of date interval you want to use (e.g. "day", "month", "year", etc.)
- **startdate**: The start date of the interval
- **enddate**: The end date of the interval
Example Usage of the DATEDIFF Function
Let's say you have a table with two columns: "Order Date" and "Ship Date". You want to calculate the number of days it took to ship each order. Here's how you can use the DATEDIFF function to do that:
Days to Ship = DATEDIFF(“day”,[Order Date],[Ship Date])
This will create a new column in your table called "Days to Ship" which will contain the number of days between the Order Date and Ship Date for each row.
Using the DATEDIFF Function with Other DAX Functions
The DATEDIFF function can also be used in combination with other DAX functions to create more complex calculations. For example, you can use it with the DATEADD function to calculate the end date of a project based on the start date and duration:
Project End Date = DATEADD([Start Date], [Duration],”day”)
You can also use it with the IF function to create conditional calculations based on the time interval:
Late Delivery = IF(DATEDIFF(“day”,[Order Date],[Ship Date]) ❱ [Expected Delivery Date],”Yes”,”No”)
This will create a new column in your table called "Late Delivery" which will contain a "Yes" or "No" value based on whether the order was shipped after the expected delivery date.
The DATEDIFF function is a powerful tool for analyzing and comparing dates in your Power BI reports. It can be used in a variety of ways to create simple or complex calculations depending on your needs. By mastering the DATEDIFF function, you'll be well on your way to creating more insightful and actionable reports in Power BI.