Forum Discussion

Bassehave's avatar
Bassehave
Helper I
6 years ago
Solved

Calculate Time over multiple months

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 Tim...
Need Help
  • Abduvali's avatar
    Abduvali
    6 years ago

    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's avatar
Bassehave
Helper I
6 years ago

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's avatar
Abduvali
Skilled Sharer
6 years ago

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
 
 
  • Bassehave's avatar
    Bassehave
    Helper I
    6 years ago

    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's avatar
      Abduvali
      Skilled Sharer
      6 years ago

      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's avatar
        Bassehave
        Helper I
        6 years ago

        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 🙂

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.