In this post, we will learn how to use DAX DATEDIFF in Power BI to calculate time intervals between two dates in SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.

Also, as a part of “DAX DATEDIFF in Power BI“, we’ll cover the below topics:

• Power BI date difference in Years
• Power BI date difference in Quarter
• Power BI date difference in Months
• Power BI date difference in Weeks
• Power BI date difference in Days
• Power BI date difference in Hours
• Power BI date difference in Minutes
• Power BI date difference in Seconds

You can download the CALENDER DAX function examples file at Power BI DAX DATEDIFF Function, and don’t forget to follow Power BI Repo to get more PIBX files samples with complex DAX formulas.

---

What is DAX in Power BI?

Data Analysis Expressions (DAX) is a formula expression language explicitly designed for the management of data models, the DAX formulas include functions and operators that can be used to create formulas and expressions in Analysis Services, Power BI, and Power Pivot in Excel.

DAX in Power BI includes a library of over 200 functions, operators, that are used by report designers to perform data analysis and calculations.

How to use Date Difference in Power BI?

In Power BI, one of the instant requirements is calculating the date and the time difference between two dates in your table.

Actually, performing date differences in Power BI seems a simple task if you understand

1. How to use DAX DATEDIFF,
2. What’s the beginning and an ending point that is formatted as a date or time,
3. Specified the required interval (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR).

What’s DAX DATEDIFF function?

The DAX DATEDIFF function does exactly what you’d expect to calculate time intervals between two dates,

• It calculates the time between two dates and displays the result in seconds, minutes, hours, days, weeks, months, quarters, or years.
• It returns the count of interval boundaries crossed between two dates.

DATEDIFF Syntax

DATEDIFF (<start_date>, <end_date>, <interval>) 

• start_date is a scalar datetime value.
• end_date is a scalar datetime value Return value.
• interval is the interval to use when comparing dates, and the value can be
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

DATEDIFF Return Value

The DAX DATEDIFF in Power BI returns a whole number that is the count of interval boundaries crossed between two dates.

How to use DAX DATEDIFF in Power BI?

Before we start using the DATEDIFF in Power BI, you have first to make sure that the value given to the parameter INTERVAL is a constant and not a string. So, they should not be enclosed in double quotation marks.

• Correct Example: = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],MONTH)
• Wrong Example: = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],"MONTH")

Calculate Power BI DATEDIFF in YEARS

Consider you have a simple table of dates in Power BI as below, and you would need to calculate the date differences between the Start and End Date columns in YEAR for each row in a table in Power BI

Steps

1. In Power BI Desktop, Click on Data Mode.

2. From the left side, Click on the table that you need to add a new column.
3. In “Table Tools” tab, click on “New Column” to create a new Column using DAX.

4. Write the below fromula to calculate DATE diffrencess between two dates in YEAR using DAX DATDIFF in Power BI.

Years between = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],YEAR)

Output

Calculate Power BI DATEDIFF in QUARTERS

Again, using the same table, you may need to calculate the date differences between the Start and End Date columns in Quarter for each row in a table in Power BI

Steps

1. In Power BI Desktop, Click on Data Mode.

2. From the left side, Click on the table that you need to add a new column.
3. In “Table Tools” tab, click on “New Column” to create a new Column using DAX.

4. Write the below fromula to calculate DATE diffrencess between two dates in Quarter using DAX DATDIFF in Power BI.

Quarter between = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],Quarter)

Output

Calculate Power BI DATEDIFF in MONTHS

Again, using the same table, you may need to calculate the date differences between the Start and End Date columns in Months for each row in a table in Power BI

Steps

1. In Power BI Desktop, Click on Data Mode.

2. From the left side, Click on the table that you need to add a new column.
3. In “Table Tools” tab, click on “New Column” to create a new Column using DAX.

4. Write the below fromula to calculate Date Diff in Month for each row in a table in Power BI

Monthes between = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],MONTH)

Output

Note, the Start Date and End Date must be in a Date Format

Calculate Power BI DATEDIFF in DAYS

In this example, we will calculate the differences between two dates in Days using DAX DATDIFF in Power BI.

Steps

1. In Power BI Desktop, Click on Data Mode.

2. From the left side, Click on the table that you need to add a new column.
3. In “Table Tools” tab, click on “New Column” to create a new Column using DAX.

4. Write the below fromula to calculate DATE diffrencess between two dates in Days using DAX DATDIFF in Power BI.

Days  between = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],DAY)

Output

Calculate Power BI DATEDIFF in MINUTES

In this example, we will calculate the differences between two dates in Minutes using DAX DATDIFF in Power BI.

Steps

1. In Power BI Desktop, Click on Data Mode.

2. From the left side, Click on the table that you need to add a new column.
3. In “Table Tools” tab, click on “New Column” to create a new Column using DAX.

4. Write the below fromula to calculate DATE diffrencess between two dates in Minutes using DAX DATDIFF in Power BI.

Minutes between = DATEDIFF('Dates Table'[Sart Date],'Dates Table'[End Date],MINUTE)

Output

---

Using DATEDIFF in Measure in Power BI

In Power BI, you can also use the DAX DATEDIFF function with constant dates in a measure to calculate the difference months between dates 2020,4,1 and 2021,3,31.

Monath = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), MONTH)

As we earlier mentioned, we can also change the interval to be one of the following “SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR”

YearDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), YEAR) //it returns 1
QuarterDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), QUARTER) //it returns 3
MonthDateDIff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), MONTH) //it retrnes 11
DayDateDIff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), DAY) //it retrnes 364
WeekDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), WEEK) //it returns 52
HourDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), HOUR) //it returns 8736
MinuteDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), MINUTE) //it returns 524K
SecondDateDiff = DATEDIFF (DATE (2020,4,1), DATE (2021,3,31), SECOND) //it returns 31M

Why DATDIFF returns negative value in Power BI

If start_date is larger than end_date, the DAX DATEDIFF function will return a negative value as shown below

Measure = DATEDIFF (DATE (2022,4,1), DATE (2021,3,31), MONTH)

Calculate DATEDIFF between FirstDate and LastDate in Power BI

Also, you can create measures that use the DAX DateDiff() function to calculate the months between the FIRSDATE and LASTDATE in your table.

MonthDurationMeasure = DATEDIFF(FIRSTDATE('Table'[Sart Date]), LASTDATE('Table'[End Date]), MONTH)

Power BI date difference from today

In this example, we can also use the DATEDIFF function to calculate the years between a specified date and the current date TODAY().

Years between End date and today = DATEDIFF(2020,4,1 ,TODAY(),YEAR)

Also, we can calculate the years between a specified column date and Today.

Years between date and today = DATEDIFF('Table'[End Date],Today(),Year)

---

Conclusion

The DATEDIFF function is a simple function that you can use to calculate the time difference between two dates in Power BI.

In this article, we have provided many examples for DATEDIFF function in Power BI to calculate:

• Power BI DATEDIFF in Years
• Power BI DATEDIFF in Quarter
• Power BI date DATEDIFF in Months
• Power BI date DATEDIFF in Weeks
• Power BI date DATEDIFF in Days
• Power BI date DATEDIFF in Hours
• Power BI date DATEDIFF in Minutes
• Power BI date DATEDIFF in Seconds

Download

You can download the CALENDER DAX function examples file at Power BI DAX DATEDIFF Function, and don’t forget to follow Power BI Repo to get more PIBX files samples with complex DAX formulas.

See Also

• How to use DAX DAY Function in Power BI?
• How to use Date function in DAX Power BI?
• How to use Power BI DAX CALENDERAUTO?

Join us

• Subscribe to Power Platform Geeks.
• Register to Saudi Arabia Power Platform User Group.

Need Help

• Have a related question? Please ask it at deBUG.to Community.