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

     

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

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's avatar
NB689
Icon for Helper I rankHelper I
4 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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'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