Calculate Time Differences in Power BI: DAX vs M Code

In this post, we'll explore two different methods for tackling this problem: DAX and M code in Power Query. Let's get it.

Method 1: The DAX Way

TimeDifference =

VAR SecondsDiff = DATEDIFF([Input_Time], [Output_Time], SECOND)

VAR Hours = INT(SecondsDiff / 3600)

VAR Minutes = INT(MOD(SecondsDiff, 3600) / 60)

VAR Seconds = MOD(SecondsDiff, 60)

RETURN FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

Quick, dirty, effective. The formula calculates the difference in seconds (as the base to form all other calculations) and then works up to minutes and hours.

But if your dataset is getting heavier, you're better using another strategy.

Method 2: The Power Query Way

let

DurationDiff = [Input_Time] - [Output_Time],

TotalSeconds = Duration.TotalSeconds(DurationDiff),

Hours = Number.IntegerDivide(TotalSeconds, 3600),

Minutes = Number.IntegerDivide(TotalSeconds - (Hours * 3600), 60),

Seconds = TotalSeconds - (Hours * 3600) - (Minutes * 60)

in

Text.PadStart(Text.From(Hours), 2, "0") & ":" & Text.PadStart(Text.From(Minutes), 2, "0") & ":" & Text.PadStart(Text.From(Seconds), 2, "0")

This code does exactly the same thing; however, it offloads the calculation effort to Power Query.

DAX vs M Code: Which One to Choose?

So, why would choose one over the other?

Performance: If your data model is large and performance is a concern, it's generally better to perform calculations using M code in Power Query. This way, the time difference is calculated during the data transformation stage, which can improve the overall performance of your report.

Flexibility: On the other hand, if you need more flexibility, such as the ability to change the time difference calculation on the fly or use it within other DAX measures, then DAX might be the better choice.

Familiarity: Finally, if you're more familiar and comfortable with one language over the other, it might make sense to stick with what you know best.

Both DAX and M code have their pros and cons. Ultimately, the choice depends on your specific needs, familiarity with the languages, and the performance requirements of your data model.

File Download here.

‍