cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayankvats Frequent Visitor
Frequent Visitor

Calculate Average Login Duration

HI All,

 

I need to show average login duration in card visual, which can be filtered by Agent name slicer, for example: In below dataset Agent A first login time is 09:10 and last logout time is 16:30 for 1st Aug, Now Login Duration of agent A will be  07:20 (16:30 - 09:10) for 1st Aug, same as of agent B & C for 1st Aug & 2nd Aug.

 

We are considering day first login as login time and day last logout as logout time.

 

AgentNameDateLoginLogout
A01-Aug09:1009:50
B01-Aug09:1510:40
A01-Aug10:0514:30
B01-Aug11:0013:00
B01-Aug13:1515:00
C01-Aug08:3010:30
C01-Aug10:3511:45
A01-Aug15:0016:30
C01-Aug12:0017:00
B01-Aug15:3016:45
B02-Aug08:5011:30
A02-Aug09:3012:30
C02-Aug09:1512:15
C02-Aug12:3014:00
B02-Aug12:0014:30
A02-Aug13:0015:30
B02-Aug15:0017:00
A02-Aug16:0016:30
C02-Aug14:1516:45

 

I am looking for your support. Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate Average Login Duration

@mayankvats,

 

You may refer to the following steps.

1) add a calculated table

Table2 =
SUMMARIZE (
    Table1,
    Table1[AgentName],
    Table1[Date],
    "Duration", DATEDIFF ( MIN ( Table1[Login] ), MAX ( Table1[Logout] ), MINUTE )
)

2) add a measure

Measure =
FORMAT ( TIME ( 0, AVERAGE ( Table2[Duration] ), 0 ), "HH:mm" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
hemantsingh Member
Member

Re: Calculate Average Login Duration

Hi @mayankvat,

 

    You can add a calculated column to your query like this.

 

Time Diff = DATEDIFF(Temptable[Login],Temptable[Logout],MINUTE)

 

Then add a measure like this for calculating averages.

 

Avg Time = Average(Temptable[Time Diff])

 

Then go to visuals & put your agent name in slicers. I am sure it will resolve your problem.

 

Regards

 

 

 

Community Support Team
Community Support Team

Re: Calculate Average Login Duration

@mayankvats,

 

You may refer to the following steps.

1) add a calculated table

Table2 =
SUMMARIZE (
    Table1,
    Table1[AgentName],
    Table1[Date],
    "Duration", DATEDIFF ( MIN ( Table1[Login] ), MAX ( Table1[Logout] ), MINUTE )
)

2) add a measure

Measure =
FORMAT ( TIME ( 0, AVERAGE ( Table2[Duration] ), 0 ), "HH:mm" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mayankvats Frequent Visitor
Frequent Visitor

Re: Calculate Average Login Duration

Hi v-chuncz-msft ,

 

Thanks a lot .... It worked prefectly for me.