Enabling Time Measure to Average

[NB689]

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	9:00 AM
B	11:00 AM	12:00 PM
C	12:00 PM	1:00 PM
C	2:00 PM	3:00 PM
C	3:30 PM	4:00 PM

I plan to create a card that shows the "Average Time Logged In". The average for this would be the total amount of time logged into the system divided by the total number of Users. The format that I am looking to achieve is "2 hr: 0M" (based on a total of 6 hours logged in, divided by 3 users).

Thank you

[tamerj1]

Hi @NB689 
Please use the following 

Average Time Logged In = 
VAR TotalTime =
    SUMX ( 
        Data,
        DATEDIFF ( Data[Login Time], Data[Logout Time], MINUTE )
    )
VAR NumberOfUsers = COUNTROWS ( VALUES ( Data[User ID] ) )
VAR AverageTime = DIVIDE ( TotalTime, NumberOfUsers )
VAR Hours = QUOTIENT ( AverageTime, 60 )
VAR Minutes = MOD ( AverageTime, 60 )
VAR Result =
    Hours & "hr:" & Minutes & "M"
RETURN
    Result

[NB689]

This looks great, but it looks like I failed to include another factor with my source data. It looks like some of the logins can span across multiple days, and I really want to calculate the average time logged in per user per DAY. I have tried to add a variable to calculate the number of days and divide the value by this, but I can't figure out the syntax. Could you show me how to add that in? Really appreciate the help.

[tamerj1]

Hi @NB689 

this is not as simple as you would expect. You have two options: either to expand your table by splitting it over days or to iterate over a Date table. Both requires  a bit complex dax. Please share more realistic sample of data and I look into it tomorrow morning. Good night 

[NB689]

I might be close on what I want the logic to do by adding in another variable for the number of days, and then adding this in to be divided in the measure.

I do have one issue though. I have added a TotalDays variable into the measure and I made a "Days Duration" measure for troubleshooting to make sure this is calculating correctly. I need the total to show 9 (the actual sum) instead of 5.

Here is my new measure:

Average Time Logged In Per User Per Day = 
VAR TotalTime =
    SUMX ( 
        Sheet1,
        DATEDIFF ( Sheet1[Login], Sheet1[Logout], MINUTE )
    )
VAR TotalDays =
    SUMX ( 
        Sheet1,
        DATEDIFF ( Sheet1[Login], Sheet1[Logout], Day )
    ) +1
VAR NumberOfUsers = COUNTROWS ( VALUES ( Sheet1[Full Name and 3-4] ) )
VAR AverageTime = DIVIDE ( TotalTime, NumberOfUsers )
VAR AverageTimePerDay = DIVIDE ( AverageTime, TotalDays )
VAR Hours = QUOTIENT ( AverageTimePerDay, 60 )
VAR Minutes = MOD ( AverageTimePerDay, 60 )
VAR Result =
    Hours & "hr:" & Minutes & "M"
RETURN
    Result

Here is what my data currently looks like. The Total for Days Duration should be 9 instead of 5.

I've tried using other commands than SUMX to produce the 9, but haven't been able to figure out how to do that. The DAX for the Days Duration column is the exact same I'm using for the TotalDays variable.

[tamerj1]

@NB689 

I typed this one the phone so please forgive me if any mistake. 

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

[NB689]

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 · Accepted solution]

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