Forum Discussion

mayankvats's avatar
mayankvats
Regular Visitor
9 years ago
Solved

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.

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

     

     

     

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    9 years ago

    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's avatar
      mayankvats
      Regular Visitor
      9 years ago

      Hi v-chuncz-msft ,

       

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