Calculate Time over multiple months

[Bassehave]

Hello everyone,

I'm having an issue getting an average of time over multiple months. Let me give an example. I'm converting Time to Decimal to be able to multiply with Calls.

Date	Calls	Time	Converted	Total
Day 1	100	00:04:00	0,00277777777777778	100 * 0,00277777777777778 = 0,277778
Day 2	5	00:20:00	0,0138888888888889	5 * 0,0138888888888889 = 0,069444
Total	105			0,277778 + 0,069444 = 0,347222

And the final calculation is: 0,347222 / 105 = 0,003307. If I convert 0,003307 to Time the result is: 00:04:46

If I simply took the average of the two Times, I would get 00:12:00 instead, which is wrong.

I created a measure [Calls] where I summed the whole Calls column. I did the same for Time (after converting to Decimal). If I pull the sum of Time into the report I get the correct decimal number, but when trying to do the measure [Time] / [Calls] the result is totally wrong. Also when I pulled the [Time] measure into the report, I'm not able to convert that decimal number back into a time format.

Do any of you know how to do a proper time calculation in Power BI?

Thanks in advance,

[Greg_Deckler]

See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Abduvali]

Hi @Bassehave ,

Just create new column and convert you time into seconds and use it to calculate your average time.

Create a new measure using logic below (it will display Avg Time in time format in the end):

Avg Talk Time =

var TimeInSeconds = SUM(Phone_Fact_Call[Duration Seconds]) / calls)

var a = INT((TimeInSeconds)/3600)

var b = LEFT(INT(MOD(TimeInSeconds,3600)/60),2)

var c = ROUND(LEFT(MOD(MOD(TimeInSeconds,3600),60),2),0)

var h = IF(LEN(a)=1,CONCATENATE("0",a),CONCATENATE("", a))

var m = IF(LEN(b)=1,CONCATENATE("0",b),CONCATENATE("",b))

var s = IF(LEN(c)=1,CONCATENATE("0",c),CONCATENATE("",c))

return

CONCATENATE(h,CONCATENATE(":",CONCATENATE(m,CONCATENATE(":",s))))

Mark as solved if this helps =D

Regards

Abduvali

[Bassehave]

Hello Abduvali,

With such a fast reply, I'm sure you are onto the correct solution for me. However, I'm not quite sure what to do with all this code.

Any chance you could be alittle more specific on what I need to do inside Power BI Desktop, to use the solution?

When going into the Query and clicking "Custom Column" it gives me a box with "=" as the only text in it, then copy pasting this:

var TimeInSeconds = SUM(Phone_Fact_Call[Duration Seconds]) / calls)

var a = INT((TimeInSeconds)/3600)

var b = LEFT(INT(MOD(TimeInSeconds,3600)/60),2)

var c = ROUND(LEFT(MOD(MOD(TimeInSeconds,3600),60),2),0)

var h = IF(LEN(a)=1,CONCATENATE("0",a),CONCATENATE("", a))

var m = IF(LEN(b)=1,CONCATENATE("0",b),CONCATENATE("",b))

var s = IF(LEN(c)=1,CONCATENATE("0",c),CONCATENATE("",c))

return

CONCATENATE(h,CONCATENATE(":",CONCATENATE(m,CONCATENATE(":",s))))

But it gives me an error in the window.

Thanks in advance for your support, it's much appreciated.

[Abduvali]

@Greg_Deckler ,

No, the code is for a MEASURE.

In Edit Query mode find your time column and convert it to secods!

Do you know how to achieve the following???

If yes, then just use that TimeInSeconds column in the code provided below: (this code you will use to create NEW MEASURE in the report view)

Avg Time = 

var TimeInSeconds = SUM(YourTableName [Yuor TimeInSeconds column]) / calls)

var a = INT((TimeInSeconds)/3600)

var b = LEFT(INT(MOD(TimeInSeconds,3600)/60),2)

var c = ROUND(LEFT(MOD(MOD(TimeInSeconds,3600),60),2),0)

var h = IF(LEN(a)=1,CONCATENATE("0",a),CONCATENATE("", a))

var m = IF(LEN(b)=1,CONCATENATE("0",b),CONCATENATE("",b))

var s = IF(LEN(c)=1,CONCATENATE("0",c),CONCATENATE("",c))

return

CONCATENATE(h,CONCATENATE(":",CONCATENATE(m,CONCATENATE(":",s))))

Let me know if you have any other questions.

Abduvali

[Greg_Deckler]

OK, it is for a measure, can still do Custom Formatting for a measure using the article I referenced. I'm not sure why that makes a difference.

[Bassehave]

Hello Abduvali,

I have removed all of my old formatting to start from the beginning.

Now I converted my time column into the "time" format. I don't see anywhere to convert it to seconds? Under "Date & Time Column" I can choose Seconds, but that only extracts seconds from the time.

Am I missing something here?

[Abduvali · Accepted solution]

@Bassehave ,

Its not that simple 🙂

You can do it in number of steps.

1. Change your time column to text format. So it will look like normal time format but its text 00:04:00 
2. Split by delimiter :
3. You will have 3 columns (One for HOURS, second is MINUTES and third is SECONDS) change type to WHOLE NUMBER
4. From Add Column tab select CUSTOM COLUMN, name this column CustomH 
   1. HOURS * 3600 (thats number of seconds in one hour)
5. From Add Column tab select CUSTOM COLUMN, name this column CustomM
   1. MINUTES * 60 (number of seconds in one minute)
6. From Add Column tab select CUSTOM COLUMN, name this column TimeInSeconds
   
   1. CustomH + CustomM + SECONDS
7. Close & Apply

Now you converted your time into seconds =D

Then create new measure and copy and paste my logic but just use your table name and column name.

Hope it clear now.

Abduvali

[Bassehave]

Thanks a lot for the clarification!

I will stop looking for the one-button-does-all 🙂

And again, thanks a lot for your support, I have marked the reply as a solution 🙂

[Abduvali]

@Bassehave ,

No problem mate. Glad it worked!

Regards

Abduvali