Step 1: Familiarize yourself with the dataset
In this example, we would like to analyze the root cause of a somewhat slow hiring process for an imaginary corporate. We will calculate the number of days that passed since the last HR interview and today.
Our dataset includes a list of candidates, for each we store the date of the data of their Final Interview Date.
Step 2: Find number of days overdue in Power BI
To find number of days since the last HR interview, we need to subtract that column from today’s date:
- In your Power BI Report, open the Data View.
- From the Data Pane, select the table name (in our case, its HR).
- Then from the Ribbon Calculations section hit new column.
- Write the following DAX statement in the formula field to create a new calculated column
Days since HR Interview = DATEDIFF (HR [HR Final Interview],TODAY(), DAY)- This will add a new column to your data model showing the gap in days between the interview and today.
In the same fashion you can calculate the number of years, months, weeks, hours from today.
Important Note: the last parameter of the DATEDIFF function represents the time unit in which we would like to express the difference between the two dates. Failing to specify a time unit will return the following error:
Too few arguments were passed to the DATEDIFF function. The minimum argument count for the function is 3.
Step 3: Incorporate your column to a Report
- Move to the Report View.
- Add a new page to your report (if needed).
- Add a new table visual and add the relevant fields as shown below. You can choose whether to summarize your table data if needed (Sum, Count, Average etc’)
- Here’s our table visual:
