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: