Duration data type not converting in the PowerBI Desktop

[Lena85]

Hi!

I am trying to build lag analysis. For that, I have a view with timestamps and various lags between them. I so far did two things. 

-> calculated each lag into dd hh mm format using DAX, however these results are only valid on row level, and I am interested in averages. 

-> Changed the original column with a lag, into a duration type. I can then see, in power query, that for that given parcel, the duration is expressed in hours. See example of the first parcel, it shows 10h 11 min 36 sec. However, when closing and applying, after refreshing, the desktop report still shows the same lag as a fraction of a 24h (see on the left), 0.42. 

How do I convert into a duration format that is easily readable to the business and on which I can calculate averages?

Thank you,

Lena

[Lena85]

@v-yinliw-msft 

Thank you for your suggestion. However when I replicate it, I get the following error:

Some of our lags have negative values as we calculate period from B - A, but sometimes, in our processes, the B can take place before A. I then cannot convert this to time. Is there any solution to this?

[v-yinliw-msft · Accepted solution]

Hi @Lena85 ,

I reproduce your question.

In the Power Query:

In the table:

You can change the type of the data like this:

Then calculate the average:

Average = FORMAT(AVERAGE('Table'[Time]),"hh:mm:ss")

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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