How to Compare Two Date Columns by Time Logic in Power BI

During analysis, you might want to compare two dates to determine the time range, for example, comparing customer order dates and shipped dates. In Power BI, you can compare two date columns by time logic comparison.

In this article, I will explain a step-by-step guide on how you can compare two date columns by time logic in Power BI.

Let’s get started.

Introduction

To compare two date columns by time logic in Power BI, you can follow these steps:

Create a calculated column

To create a new calculated column, go to the Modeling tab in the Power BI Desktop ribbon and click New Column. You can name the column Name the column as desired.

Use the DATEDIFF function

In the formula bar of the new column, use the DATEDIFF function to calculate the time difference between the two date columns. This function takes three arguments: the date/time unit to use for the calculation, the start date, and the end date.

For example, if you want to calculate the time difference between an order date and a shipment date in hours, you can use the following formula:

Time_Difference_In_Hours = DATEDIFF(HOUR, [Order_Date], [Shipment_Date])

This formula calculates the time difference in hours between the Order_Date and the Shipment_Date columns.

Use the IF function for comparison

Now that you have calculated the time difference in your new column, you can use the IF function to set a threshold for the time difference.

The IF function also takes three arguments: the condition to check, the value to return if the condition is true, and the value to return if the condition is false.

For example, if you want to check if an order was shipped within 24 hours of the order date, you can use the following formula:

ShippedWithin24Hours = IF(DATEDIFF(HOUR, [Order_Date], [Shipment_Date]) <= 24, "Yes", "No")

This formula checks if the time difference between the Order_Date and the Shipment_Date columns is less than or equal to 24 hours. If it is, the formula returns “Yes”, otherwise it returns “No”.

Add the calculated column to your visual

Once you have created the calculated column, you can add it to a visual by dragging and dropping it onto the visual.

You can use any type of visual that you want, such as a matrix table, chart, etc.

FAQs

What is time logic comparison in Power BI?

Time logic comparison is a process of comparing two date columns or timestamp columns and calculating the time difference between them.

This time difference to perform analysis, such as determining if an order was shipped within 24 hours of the order date.

Can you compare more than two date columns in Power BI?

Yes, you can compare more than two date columns in Power BI by creating additional calculated columns and using the same procedures explained in this article.

What kind of visuals can you use to display time logic comparisons in Power BI?

The type of visual you use depends on the specific analysis you are performing and the way you want to display the data.

However, you can use any type of visual such as tables, charts, or matrices.

What are time intelligence functions?

Time intelligence functions are a set of functions in Power BI that analyze data over a period of time. Common examples include TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD, TOTALMTD, DATESINPERIOD, etc.

Conclusion: How to Compare Two Date Columns by Time Logic in Power BI

And that is it!

By following these steps, you can be sure to compare two date columns by time logic in Power BI, and you can adjust the formulas and thresholds explained in this article to fit your specific needs.

I hope you understood these steps.

Thanks for reading