Power BI's DAX functions can be used to perform a wide range of specific calculations on data. This is a vast collection of ready-to-use formulas that can be applied to any numbers, such as Power BI DATEDIFF.
One of these DAX functions is DATEDIFF. It is used in Power BI to calculate time intervals between two dates. This interval can be expressed in seconds, minutes, hours, days, weeks, months, quarters or years.
What is Power BI?
Power BI is a complete Business Intelligence platform developed by Microsoft. It brings together a number of tools dedicated to business intelligence, in particular for collecting, analysing and displaying data.
The platform is available as local software, but also as a cloud service on Microsoft Azure and as a mobile application on iOS and Android. It integrates with the software in the Office 365 suite.
What is DAX on Power BI?
Data Analysis Expressions or DAX is a formula expression language explicitly designed for data model management. DAX formulas include functions and operators that can be used to create formulas and expressions on Power BI, Power Pivot on Excel, and on the Azure cloud.
In total, DAX includes over 200 formulas. They are used to analyse data and produce reports.
What is Power BI DATEDIFF?
When using Power BI, it is often necessary to calculate the time differences between two dates in a table. This may seem a complex task, but all you need to do is use the DAX DATEDIFF formula.
The only requirement is that you know what the start and end points are in date or time format, and that you can specify the type of interval you want.
The DAX DATEDIFF function calculates the time interval between two dates, and presents the result in seconds, minutes, hours, days, weeks, months, quarters or years.
The syntax of Power BI DATEDIFF is: “DATEDIFF (, , )”.
The “start_date” parameter is a scalar date or time value, in the same way as the “end_date” parameter. The ‘interval’ parameter is the interval to be used to compare dates, and can be ‘SECOND’, ‘MINUTE’, ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’ or ‘YEAR’.
As a result, Power BI’s DAX DATEDIFF formula returns an integer. This is the number of intervals crossed between the two dates.
How do you calculate the interval between 2 dates with Power BI DATEDIFF?
Before you start using DATEDIFF on Power BI, make sure that the value given for the “INTERVAL” parameter is a constant and not a line.
It must not be surrounded by double inverted commas.
The correct syntax would therefore be ” = DATEDIFF(‘Dates Table'[Sart Date],’Dates Table'[End Date],MONTH)” and not ” = DATEDIFF(‘Dates Table'[Sart Date],’Dates Table'[End Date], “MONTH”)”.
If you have a table of dates in Power BI and want to calculate the date differences between the start date and end date columns for each row, here’s how to do it.
From Power BI Desktop, click on “Data Mode”. On the left, click on the table for which you want to add a new column. In the “table tools” tab, click on “new column” to create a new column using DAX.
Then simply write the following formula to calculate the date differences between two dates in years: “Years between = DATEDIFF(‘Dates Table'[Start Date],’Dates Table'[End Date],YEAR)”.
To measure the date difference using another type of interval, simply change the value of the “interval” parameter. For example, to calculate the difference in months, enter the formula: “Months between = DATEDIFF(‘Dates Table'[Start Date],’Dates Table'[End Date],MONTH)”.
How do I use Power BI DATEDIFF in a measurement?
Power BI’s DAX DATEDIFF function can also be used with constant dates in a measurement, to calculate the two-month difference between two fixed dates such as 2020,3,1 and 2022,3,31.
Simply enter the formula: “Months = DATEDIFF (DATE (2020,3,1), DATE (2022,3,31), MONTH)”. Once again, the ‘MONTH’ interval can be replaced by ‘SECOND’, ‘MINUTE’, ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’ or ‘YEAR’.
-- DATEDIFF computes the delta between two dates, using different units of measure
-- YEAFRAC returns the delta as a fraction (in years)
EVALUATE
VAR StartDate = DATE ( 2011, 01, 01 )
VAR EndDate = DATE ( 2012, 12, 15 )
RETURN
{
( "DATEDIFF Year", DATEDIFF ( StartDate, EndDate, YEAR ) ),
( "DATEDIFF Quarter", DATEDIFF ( StartDate, EndDate, QUARTER ) ),
( "DATEDIFF Month", DATEDIFF ( StartDate, EndDate, MONTH ) ),
( "DATEDIFF Day", DATEDIFF ( StartDate, EndDate, DAY ) ),
( "Subtraction-1", INT ( EndDate - StartDate ) ),
( "Subtraction-2", CONVERT ( EndDate - StartDate, INTEGER ) ),
( "YEARFRAC", YEARFRAC ( StartDate, EndDate ) )
}
Note that if the “start_date” parameter is larger than the “end_date” parameter, the DATEDIFF function returns a negative value. This is a syntax error that needs to be corrected.
How do you calculate the difference between FIRSTDATE and LASTDATE?
It is also possible to create measurements using the DAX DATEDIFF function to calculate the months between the FIRSTDATE (first date) and the LASTDATE (last date) of the table.
To do this, simply enter the formula: “MonthDurationMeasure = DATEDIFF(FIRSTDATE(‘Table'[Start Date]), LASTDATE(‘Table'[End Date]), MONTH)”.
How do you calculate the date difference from today?
Finally, you can use the Power BI DATEDIFF function to calculate the years separating a specific date from today’s date. The formula is “Years between End date and today = DATEDIFF(2020,4,1 ,TODAY(),YEAR)”.
Similarly, there is a formula for calculating the years between a column date and today: “Years between date and today = DATEDIFF(‘Table'[End Date],Today(),Year)”.
How do I master Power BI?
The Power BI DATEDIFF function is very useful for calculating the time difference between two dates on Power BI. However, it is just one of the many DAX formulas available.
To learn how to handle Power BI and all its more subtle functions, you can choose DataScientest. Our training is entirely distance learning, over 2 to 5 days.
You can choose between the three-day beginner programme or the two-day advanced programme. The “complete mastery” course combines the two programmes and lasts 38 hours spread over five days.
The first part of the course teaches you how to use the various Power BI tools, such as the DAX language and the Power Query engine. You will also learn how to analyse data using the platform and create dashboards for decision-making purposes.
The second part takes you deeper into the use of the tool. You will learn to use each service in a more advanced way, and discover the more complex notions of DataFlows and Incremental Refresh.
All our teachers have Microsoft Certified Trainer status, and the course includes the Microsoft PL-300 Power BI Data Analyst Associate certification exam.
For funding, our government-recognised organisation is eligible for state financing. Discover DataScientest now to master Power BI and learn fore about financing options!
