How to find date differences in Power BI?

Step 1: Identify your date fields

This fictitious company has a target to finalize the candidate interviewing process in less than 7 (seven) business days. To analyze the situation we will calculate the time between first interview and the signing of the employment agreement.

Use the Model or Date Views in Power BI to locate the date fields you would like to subtract. In our case, both fields are located in the in the HR table as shown below:

Note: In a more complex scenario you might need to use a virtual table to join dates stored in different tables and then make your calculations.

Step 2: Subtract dates in Power BI

To find the number of business days between dates in Power BI:

  • In Power BI, highlight your table and open the Data View.
  • From the Ribbon, under Calculations, hit New Column.
  • Write the following DAX statement:
Days until signature = NETWORKDAYS (HR[First  Interview Date],HR[Employment Agreement Date])

Note: In case that you just need the number of days (including weekends you can use the following code

Days until signature = DATEDIFF(HR[First  Interview Date],HR[Employment Agreement Date], DAY)

Note: similarly you can calculate time differences in hours, weeks, months etc’.


Step 3: Show calculated date differences in a Power BI visual

Now we would like to create a short report and highlight the cases in which the company is exceeding its policy of eight days max for the hiring process.

  • From the Visualization pane, double click on a table or matrix.
  • From the Data Pane, check the relevant fields, or drag them onto your table or matrix.
  • Using Your table Conditional Formatting function, set the background color of your KPI as needed. In our case, we are interested to mark all processes which length was 8 working days or more.

Here’s our chart: