If you’re an avid Power BI user, you know the significance of accurate data analysis and visualization. When it comes to working with date-related information, calculating the difference between two dates becomes crucial for gaining valuable insights and making informed decisions.

Whether you’re measuring the duration between two events, tracking project timelines, or evaluating the time elapsed since a specific milestone, Power BI offers a wealth of functionalities to handle date calculations efficiently.

In this blog post, you will learn various techniques and formulas that will help you calculate the difference between two dates with precision in Power BI.

Calculate the Days Between Two Dates in Power Query

In Power Query, you can use the Custom Column feature or Advanced Editor to calculate the difference between two dates.

Using Add Columns

Power Query’s Add Column holds a plethora of options for generating new columns. When it comes to calculating the difference between two dates, the Custom Column menu is your go-to tool. Its ability to not only perform the calculation but also place the resulting values into a new column makes it ideal for this task.

With the Custom Column command, you can effortlessly calculate the date difference and simultaneously create a dedicated column to hold the calculated values. This streamlined approach eliminates the need for multiple steps and simplifies the entire process.

Duration.Days([Ship Date] - [Order Date])

Clicking on Custom Column will open up the Custom Column dialogue box. Within this dialogue box, you’ll find a formula box where you can conveniently copy and paste the syntax provided above.

The syntax employs the Duration.Days M function to extract the number of days between the Ship Date and Order Date for each row.

At the bottom left of the window, you’ll find the “No syntax errors have been detected” statement, which serves as a helpful indicator to verify the accuracy of your formula. This statement acts as a quick check to ensure that there are no errors present within the formula you’ve entered.

After verifying and ensuring that everything is in order, you can proceed by clicking on the OK button.

Now you have created a new column that shows the difference between the date in each row.

To change the format of the column to whole numbers, right-click on the icon situated on the left side of the column name. From the contextual menu that appears, select the option labelled “Whole Number.” By following these steps, you’ll successfully adjust the column format to whole numbers.

Using the Advanced Editor

The Advanced Editor in Power Query is a powerful tool for achieving complex data transformations. It grants direct access to the underlying M code for each operation, enabling you to dive deeper into Power Query’s functionality.

With the Advanced Editor, you can create custom transformations beyond the standard interface. You get the same result as when you use the Custom Column feature. If you’re someone who feels comfortable and enjoys writing M code, this option becomes particularly valuable for you as it allows you to leverage your coding skills to their fullest potential.

To use the Advanced Editor to replace null values, go to the Query section in the Home tab.

When you access the Advanced Editor dialogue box in Power Query, you’ll uncover the source code responsible for driving the data transformations. These syntaxes are generated automatically every time you take an action that changes or modifies the data. Consequently, every line of code represents an action. By exploring this code, you gain insight into the underlying operations that shape your data.

#”Added Custom” = Table.AddColumn(#"Kept Range of Rows", "Date Difference", each Duration.Days([Ship Date] - [Order Date]), Int64.Types)

Add this syntax to the advanced editor. But before you do this, add a comma to the last line of code.

The #”Added Custom” syntax uses the table function Table.AddColumn function to create a new column. The function has the following parameters:

• table – a reference to a table where the new column will be added.
• newColumnName – the name of the new column. Must be in text, hence, the name must be enclosed in quotation marks.
• columnGenerator – the formula used to generate the values for the new column.
• columnType – this parameter defines the column format for the new column. This is an optional parameter.

In the syntax, the table argument in the form of #”Kept Range of Rows” is used because it returns a table. The parameter “Date Difference” specifies the newColumnName, while each Duration.Days([Ship Date] – [Order Date])” defines the columnGenerator, calculating the difference between the [Ship Date] and [Order Date] for each row. The Int64.Types format is applied to ensure the column is formatted as whole numbers.

The function is assigned to the variable “Added Column” and its evaluation occurs within the In statement, allowing the new column to be added to the dataset accordingly.

To add a new line of code to the Let statement and assign it to a variable name, simply copy and paste the syntax into the In statement of the query. In M language, the Let statement enables you to evaluate a set of values and assign them to variable names, which can then be utilized in subsequent expressions that follow the In statement.

The “No syntax errors have been detected” shows there are no errors in the syntax. So, you can click on Done to exit the dialogue window.

By utilizing the advanced editor, you can effortlessly create a new column in Power Query, much like you would through the Custom Column menu. However, the key advantage of using the advanced editor is the ability to accomplish both column creation and formatting in a single line of code. This approach saves you time and effort.

Calculate the Days Between Two Dates with DAX

Another approach to calculating the difference between two dates in Power BI is to create a calculated column using Data Analysis Expression (DAX). DAX is the formula language specifically designed for custom calculations in Power BI. By leveraging DAX, you can define calculations to analyze your data effectively.

To begin creating a calculated column, the first step is to load your data into Power BI, ensuring that the necessary dataset is readily available for your calculations.

After loading your data, click on the grid icon to view the table. Then click on the New column in the Table tools tab.

Date Diff = DATEDIFF(
    Dates[Order Date],
    Dates[Ship Date],
    DAY
)

The DATEDIFF function is used to calculate the difference between both dates. The function has the following parameters:

• <Date1> – a date value or the column containing the first date.
• <Date2> – date value or the column containing the second date. If <Date2> is greater than <Date1>, the function returns a positive result, and it returns a negative result otherwise.
• <Interval> – this parameter allows you to choose the interval you want to use for comparing the dates. This can be either one of a Second, Minute, Hour, Day, Week, Month, Quarter, or Year.

In the syntax, the [Order Date] (<Date1>) is subtracted from the [Ship Date] (<Date2>) and the interval parameter is set to DAY so that the function returns the number of days between both dates.

The result obtained when using the DATEDIFF function in Power BI is similar to the outcome achieved through Power Query.

Conclusions

Mastering the art of calculating the difference between two dates in Power BI opens up a world of possibilities for data analysis and decision-making. Through the various techniques and formulas explored in this post, you now have the tools to accurately measure durations, track timelines, and evaluate the passage of time in your datasets.

With Power BI’s robust date calculation capabilities at your disposal, you can uncover valuable insights, spot trends, and make informed business decisions based on the time-based relationships within your data. Remember to experiment, customize, and fine-tune your date calculations to suit your specific needs and analysis requirements.