Calculate Average Login Duration

[mayankvats]

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.

AgentName	Date	Login	Logout
A	01-Aug	09:10	09:50
B	01-Aug	09:15	10:40
A	01-Aug	10:05	14:30
B	01-Aug	11:00	13:00
B	01-Aug	13:15	15:00
C	01-Aug	08:30	10:30
C	01-Aug	10:35	11:45
A	01-Aug	15:00	16:30
C	01-Aug	12:00	17:00
B	01-Aug	15:30	16:45
B	02-Aug	08:50	11:30
A	02-Aug	09:30	12:30
C	02-Aug	09:15	12:15
C	02-Aug	12:30	14:00
B	02-Aug	12:00	14:30
A	02-Aug	13:00	15:30
B	02-Aug	15:00	17:00
A	02-Aug	16:00	16:30
C	02-Aug	14:15	16:45

I am looking for your support. Thanks in advance.

[v-chuncz-msft · Accepted solution]

@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" )

[mayankvats]

Hi v-chuncz-msft ,

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

[hemantsingh]

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