Forum Discussion

NB689's avatar
NB689
Icon for Helper I rankHelper I
4 years ago
Solved

Enabling Time Measure to Average

Hello,   I would like to show average time logged in for a group of users based on data that looks like the below table:   User ID Login Time Logout Time A 1:00 PM 2:30 PM B 8:00 AM...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi NB689 
    Actually dividing by the number of days will not solve the problem. In the sample data that I have the problem with that approach was clear. All the days have a duration of less than one day. If you divide by the number days the result of the total will be 20min instead of 2hr. You may try yourself. In the following comment will post the correct number of days formula just for reference.


    I hope this code will work as expected

    Average Time Logged In Per User Per Day = 
VAR AverageTime =
    AVERAGEX (
        VALUES ( Sheet1[Full Name and 3-4] ),
        CALCULATE ( 
            SUMX (
                Sheet1,
                VAR Login = Sheet1[Login]
                VAR Logout = Sheet1[Logout]
                VAR Minutes =
                    DATEDIFF ( Login, Logout, MINUTE )
                VAR Days =
                    DATEDIFF ( Login, Logout, DAY ) + 1
                RETURN
                DIVIDE ( Minutes, Days )
            )
        )
    )
VAR Hours =
    QUOTIENT ( AverageTime, 60 )
VAR Minutes =
    FORMAT ( MOD ( AverageTime, 60 ), "00" )
VAR Result = Hours & "hr:" & Minutes & "M"
RETURN
    Result

     

NB689's avatar
NB689
Icon for Helper I rankHelper I
4 years ago

This looks close, but I still need the number it looks like it is totaling the averages and not taking into account the number of days in the duration. So the total here is 43hr:40M but the real number should be 4 hours and 51 minutes (2,619 minutes /60 / number of days duration.

 

So to break it down, how do I get my Days Duration measure to sum correctly? The total for this should be 9, not 5 (see the screenshot below).

 

Here is that measure. Once I figure that out I can add it into my larger measure:

Days Duration = SUMX ( 
        Sheet1,
        DATEDIFF ( Sheet1[Login], Sheet1[Logout], Day )
    ) +1

 Here is what my data looks like now:

 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi NB689 
Actually dividing by the number of days will not solve the problem. In the sample data that I have the problem with that approach was clear. All the days have a duration of less than one day. If you divide by the number days the result of the total will be 20min instead of 2hr. You may try yourself. In the following comment will post the correct number of days formula just for reference.


I hope this code will work as expected

Average Time Logged In Per User Per Day = 
VAR AverageTime =
    AVERAGEX (
        VALUES ( Sheet1[Full Name and 3-4] ),
        CALCULATE ( 
            SUMX (
                Sheet1,
                VAR Login = Sheet1[Login]
                VAR Logout = Sheet1[Logout]
                VAR Minutes =
                    DATEDIFF ( Login, Logout, MINUTE )
                VAR Days =
                    DATEDIFF ( Login, Logout, DAY ) + 1
                RETURN
                DIVIDE ( Minutes, Days )
            )
        )
    )
VAR Hours =
    QUOTIENT ( AverageTime, 60 )
VAR Minutes =
    FORMAT ( MOD ( AverageTime, 60 ), "00" )
VAR Result = Hours & "hr:" & Minutes & "M"
RETURN
    Result