How To Calculate Time Difference In Power Bi

How to Calculate Time Difference in Power BI

To calculate the time difference between two dates or times in Power BI, you can use the DATEDIFF function. This function calculates the difference between two dates or times based on a specified interval.

Syntax:

The syntax for the DATEDIFF function in Power BI is as follows:
DATEDIFF(, , )

Example:

Let’s say we have a table with two columns – “Start Time” and “End Time” – and we want to calculate the duration between these two times.

Step 1: Load the data into Power BI and create a table or matrix visual with the “Start Time” and “End Time” columns.

Step 2: Create a new column to calculate the time difference. Right-click on the table, select “New Column” and enter the following formula:

Duration = DATEDIFF(SECONDS, [Start Time], [End Time])

In this example, we’re using the “SECONDS” interval to calculate the duration in seconds. You can replace it with other intervals like “MINUTE”, “HOUR”, “DAY”, etc., depending on your requirements.

Step 3: Convert the duration to a more readable format (hours, minutes, and seconds) if required. You can use the following formula:

Duration (Formatted) =
    FORMAT(
      [Duration] / 3600,
      "00"
    ) & ":" & FORMAT(
      MOD([Duration] / 60, 60),
      "00"
    ) & ":" & FORMAT(
      MOD([Duration], 60),
      "00"
    )

This formula divides the duration by 3600 to get the hours, takes the remainder after dividing by 60 to get the minutes, and the remainder after dividing by 60 again to get the seconds. The FORMAT function is used to format these values as two-digit numbers with leading zeros.

Result:

You should now see a new column in your table or matrix visual that shows the duration between the start time and end time, both in seconds and a formatted version with hours, minutes, and seconds.

By following these steps, you can calculate the time difference in Power BI and customize the output format according to your needs.