cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Captain_Ricard
New Member

Addition of time in a day

Hi All,

it is my first post as i have some difficulties to find an answer to my issue.

I am a service desk manager and i'am working on a power BI dashboard to replace the excel file i am using until now.

My issue is that when i want to add times, can have the sum when times are in numeric format. in date/time format i cannot have the sum only the min or max value :

 

here's a sample : 

 


Day              HHMM Entrants        Abandon_15s        Servis       Servis_30s        Talk time
16/10/2018  08:00        2               0                             2             2                       00:06:58
16/10/2018  09:00        5               0                             5             5                       00:09:33
16/10/2018  09:30        2               0                             2             1                       00:20:40
16/10/2018  10:00        4               0                             4             4                       00:02:31
16/10/2018  10:30        3               0                             3             3                       00:18:40
16/10/2018  11:00        1               0                             1             1                       00:09:43
16/10/2018  11:30        3               0                             3             3                       00:12:24
16/10/2018  13:30        3               0                             3             3                       00:04:56
16/10/2018  14:00        2               0                             2             2                       00:07:25
16/10/2018  15:00        1               0                             1             1                       00:03:16
16/10/2018  16:00        4               0                             4             4                       00:11:33
16/10/2018  16:30        1               0                             1             1                       00:17:39

 

my goal is to have the sum of talktime for a given day in order to calculate the average time of talk time. 

formula is talktime/answered calls. issue is that as my data are sliced into halfs hours i get an error (multiple values for a given day) 

question is how can i add the times for a day ?

 

thank you in advance for your help, and sorry if my english is not perfect, its not my native language

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Addition of time in a day

Hi @Captain_Ricard,

 

There could be two solutions. Please check out the demo in the attachment.

1. Add a column and convert the times into seconds which is numeric values. The calculation could be easy.

2. Integrate the solution 1 in a measure. 

 

Measure =
VAR avg_seconds =
    DIVIDE (
        SUMX ( 'Table1', DATEDIFF ( TIME ( 0, 0, 0 ), [Talk time], SECOND ) ),
        COUNT ( Table1[Abandon_15s] ),
        9999
    )
VAR hours =
    INT ( DIVIDE ( avg_seconds, 3600 ) )
VAR minutes =
    INT ( DIVIDE ( MOD ( avg_seconds, 3600 ), 60 ) )
VAR seconds =
    INT ( MOD ( MOD ( avg_seconds, 3600 ), 60 ) )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds

 

Best Regards,
Dale

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

View solution in original post

4 REPLIES 4
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Addition of time in a day

Hi @Captain_Ricard,

 

There could be two solutions. Please check out the demo in the attachment.

1. Add a column and convert the times into seconds which is numeric values. The calculation could be easy.

2. Integrate the solution 1 in a measure. 

 

Measure =
VAR avg_seconds =
    DIVIDE (
        SUMX ( 'Table1', DATEDIFF ( TIME ( 0, 0, 0 ), [Talk time], SECOND ) ),
        COUNT ( Table1[Abandon_15s] ),
        9999
    )
VAR hours =
    INT ( DIVIDE ( avg_seconds, 3600 ) )
VAR minutes =
    INT ( DIVIDE ( MOD ( avg_seconds, 3600 ), 60 ) )
VAR seconds =
    INT ( MOD ( MOD ( avg_seconds, 3600 ), 60 ) )
RETURN
    hours & ":"
        & minutes
        & ":"
        & seconds

 

Best Regards,
Dale

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

View solution in original post

Captain_Ricard
New Member

Re: Addition of time in a day

Hi @Dale

 

I tried to convert into numerioc values, but in the report view the format still in numeric and not so "understandable", when i convert the column into HH:MM format there is no calculation, only the choice of min and max as stated before.

 

So il will try the second solution you give and build a measure and revert back here to tell you the results

 

thank you for the tip and the attached example !

 

regards 

Microsoft v-jiascu-msft
Microsoft

Re: Addition of time in a day

Hi @Captain_Ricard,

 

I forgot to add the details. It's a new column with the formula.

 

Seconds = DATEDIFF(time(0, 0, 0), [Talk time], SECOND)

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Captain_Ricard
New Member

Re: Addition of time in a day

@v-jiascu-msft Dale

 

The Measure works well ! 

 

 

Here's a test :

Power BI.jpg

The measure is the column named Temps Moyen HHMM

 

Thank you again for you help

Have a nice day

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors