Question about DAX Formula for calculating time difference

[analyst123]

Hi,

I am trying to calculate the duration (in minutes) between two columns in Power BI. The columns look like this:

Unfortunately when I tried to simply add a custom column with the formula = "Column 2" - "Column 1", it only worked properly for a few of the rows but keeps giving the wrong answers for others. If I try splitting this column into two (seperating the time and date), I end up losing the seconds for the time. So if anyone can please help me with the DAX code for this or any other altenrate way?

[muhammad_786_1 · Accepted solution]

There are multiple ways to solve this problem.

You can also do it by using this measure.

Duration in Minutes=
DATEDIFF(MIN('Sheet1'[Column1]), MIN('Sheet1'[Column2]), MINUTE) +
(SECOND(MIN('Sheet1'[Column2])) - SECOND(MIN('Sheet1'[Column1]))) / 60

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

Follow on LinkedIn

[analyst123]

Hello @muhammad_786_1 ,

Thank you for your help. The formula works but there is only one issue that I am facing. It gives me negative values as shown below. Is there anyway to add another function for these specific kind of issues?

I also had one more question. The formula works well and shows the correct duration for each row. I am unable to get a total when i add it to the table.

And even when I click on the data column, it does not give me the option to make it into a total.

 I appreciate you for your help! Please let me know. Thank you!

[muhammad_786_1]

I designed the formula with the assumption that 'Column1' represents the earlier date and 'Column2' the later date. However, if there’s a possibility that 'Column2' could be an earlier date than 'Column1', you can simply put the entire calculation in the ABS() function. This will ensure that the result is always positive, regardless of the order of the dates.

For the issue with the total, we can also do this by creating a calculated column, especially if the performance impact is minimal. This approach ensures that the total is calculated correctly. You can also check this attached file.

File

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

Follow on LinkedIn

[Greg_Deckler]

@analyst123 The formula should be:

Column = ([Column2] - [Column1]) * 60 * 60