Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
3 years ago
Solved

Calculate the total working time

Hi BI Community Team,

 

We have a data of visited by sales rep and want to calculate the total working time spent that normally we have a time session/group: morning, lunch, afternoon, evening.

 

With these session, we consider the working time based on check-in (earliest) and check-in (latest).

 

Any suggestion/advise how to calculate and find out the total working time by day & month of each sales rep?

 

Thanks and Regards,

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi ADSL ,

     

    There are several approaches to this:

    1. To create a single measure of  a virtual table summarizing the earliest and latest check-ins by code, group, and date, calculate the difference between the check-in and summing up the difference.

    Time = 
SUMX (
    ADDCOLUMNS (
        //virtual table
        SUMMARIZE ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] ),
        "Earliest Check-in",
            CALCULATE (
                MIN ( Data[Check-In] ),
                ALLEXCEPT ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] )
            ),
        "Latest Check-in",
            CALCULATE (
                MAX ( Data[Check-In] ),
                ALLEXCEPT ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] )
            )
    ),
    //aggregate the difference
    [Latest Check-in] - [Earliest Check-in]
)
//multiply the result by 24 to get the time in hours

    2. Create several calculated columns for the earliest and latest check-ins and the difference. I personally prefer this as it is easy to visualize the calculations behind. Eeasier to troubleshoot as well. Result can be viewed in the data view. 

    Earliest Check-in = 
CALCULATE (
    MIN ( Data[Check-In] ),
    FILTER (
        FILTER (
            FILTER ( Data, Data[SR_CODE] = EARLIER ( Data[SR_CODE] ) ),
            Data[CALL_DATE] = EARLIER ( Data[CALL_DATE] )
        ),
        Data[Time_Group] = EARLIER ( Data[Time_Group] )
    )
)

Latest Check-in = 
CALCULATE (
    MAX ( Data[Check-In] ),
    FILTER (
        FILTER (
            FILTER ( Data, Data[SR_CODE] = EARLIER ( Data[SR_CODE] ) ),
            Data[CALL_DATE] = EARLIER ( Data[CALL_DATE] )
        ),
        Data[Time_Group] = EARLIER ( Data[Time_Group] )
    )
)

Difference = Data[Latest Check-in] - Data[Earliest Check-in]

     

    And to get the sum create a measure of a virtual table summarizing the difference by group, code and date. Outright sum of the difference column will give a wrong result.

    Time2 = 
SUMX (
    SUMMARIZE ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE], Data[Difference] ),
    Data[Difference]
)
//multiply by 24 to get the sum in hours

     

     

    Please see attached sammple pbix. 

     

1 Reply

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi ADSL ,

     

    There are several approaches to this:

    1. To create a single measure of  a virtual table summarizing the earliest and latest check-ins by code, group, and date, calculate the difference between the check-in and summing up the difference.

    Time = 
SUMX (
    ADDCOLUMNS (
        //virtual table
        SUMMARIZE ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] ),
        "Earliest Check-in",
            CALCULATE (
                MIN ( Data[Check-In] ),
                ALLEXCEPT ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] )
            ),
        "Latest Check-in",
            CALCULATE (
                MAX ( Data[Check-In] ),
                ALLEXCEPT ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE] )
            )
    ),
    //aggregate the difference
    [Latest Check-in] - [Earliest Check-in]
)
//multiply the result by 24 to get the time in hours

    2. Create several calculated columns for the earliest and latest check-ins and the difference. I personally prefer this as it is easy to visualize the calculations behind. Eeasier to troubleshoot as well. Result can be viewed in the data view. 

    Earliest Check-in = 
CALCULATE (
    MIN ( Data[Check-In] ),
    FILTER (
        FILTER (
            FILTER ( Data, Data[SR_CODE] = EARLIER ( Data[SR_CODE] ) ),
            Data[CALL_DATE] = EARLIER ( Data[CALL_DATE] )
        ),
        Data[Time_Group] = EARLIER ( Data[Time_Group] )
    )
)

Latest Check-in = 
CALCULATE (
    MAX ( Data[Check-In] ),
    FILTER (
        FILTER (
            FILTER ( Data, Data[SR_CODE] = EARLIER ( Data[SR_CODE] ) ),
            Data[CALL_DATE] = EARLIER ( Data[CALL_DATE] )
        ),
        Data[Time_Group] = EARLIER ( Data[Time_Group] )
    )
)

Difference = Data[Latest Check-in] - Data[Earliest Check-in]

     

    And to get the sum create a measure of a virtual table summarizing the difference by group, code and date. Outright sum of the difference column will give a wrong result.

    Time2 = 
SUMX (
    SUMMARIZE ( Data, Data[SR_CODE], Data[Time_Group], Data[CALL_DATE], Data[Difference] ),
    Data[Difference]
)
//multiply by 24 to get the sum in hours

     

     

    Please see attached sammple pbix.