Calculate the total working time

[ADSL]

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,

[danextian · Accepted solution]

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.