cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular 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
Community Support
Community Support

@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.

View solution in original post

3 REPLIES 3
Community Support
Community Support

@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.

View solution in original post

Hi v-chuncz-msft ,

 

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

Helper V
Helper V

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

 

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors