Making sense of a column containing day.hours:minutes:seconds

[Anonymous]

Hi there,

So I have two datetime columns and I have calculated the difference between them using:

each Duration.ToText([end date]-[start date]), type number)

I have a column with values such as 

78.08:11:05 = 78 days, 8 hours, 11 minutes, 5 seconds

00.00:54 = 0 days, 0 hours, 0 minutes, 54 seconds

As a number, this is throwing up an error when I close and apply as I guess DAX doesn't like the format. 

I am going to need to be able to translate this column into a turnaround measurement. 

Can you suggest anything to stop this erroring when I get to Report View, and also be able to sum this and have it recognised as a period of time?

[Greg_Deckler · Accepted solution]

I believe you should be using Power Query's Duration data type instead of number.

Here is something that I wrote recently for dealing with Durations in DAX:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-a...

In DAX it is more of a formatting thing since it does not have a true duration data type.

[amitchandak · Accepted solution]

In case both are DateTime. You should be able to use datediff. And if you can give diff in the required unit.

https://docs.microsoft.com/en-us/dax/datediff-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Greg_Deckler · Accepted solution]

I believe you should be using Power Query's Duration data type instead of number.

Here is something that I wrote recently for dealing with Durations in DAX:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-a...

In DAX it is more of a formatting thing since it does not have a true duration data type.

[Anonymous]

Thanks both, these both work, I prefer the dax though. Not sure why I forgot about this!