Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Time on-site

Hi Everyone, 

 

I want to see on-site time recorded by each technician by each working day, our CAFM system captures the start and the end date/time.

 

I've tired = DATEDIFF('Time Log'[StartDateTime],'Time Log'[EndDateTime],MINUTE)/60 but, doesn't appear to return the correct time, the return being Duration On-Site.

 

Total Time On-Site is correct HH:MM:SS but, it when it comes to the visualizations it only counts instances and doesn't return total hours/minutes.

 

Job ID

StartDateTimeEndDateTimeItemAssigneeDuration On-SiteTotal Time On-Site

1

26/11/2019 12:4826/11/2019 17:27On-site timeA4.6504:39:15
127/11/2019 08:1927/11/2019 17:18On-site timeA8.9808:59:40
128/11/2019 08:0028/11/2019 16:56On-site timeA8.9308:56:45
129/11/2019 09:4529/11/2019 17:25On-site timeA7.6707:39:43

 

There are some instances where the technician(s) haven't logged off so I need to also need to extract the business hours (08:00 until 17:00, Monday to Friday) within days captured:

 

Job IDStartDateTimeEndDateTimeOn-site timeAssignee
224/09/2019 08:4305/11/2019 12:11On-site timeB
217/10/2019 10:4117/10/2019 10:41On-site timeC
217/10/2019 08:0017/10/2019 09:00On-site timeC
214/11/2019 17:2621/11/2019 16:54On-site timeB
216/08/2019 08:1816/08/2019 08:18On-site timeB

 

I've looked at lots of different solutions but, I don't seem to be able to generate the correct result. 

 

Any ideas would be much appreciated. 

 

Regards,

 

Need Help

9 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    Total Time On-Site is correct HH:MM:SS but, it when it comes to the visualizations it only counts instances and doesn't return total hours/minutes.

    I don't think you can "sum" times. You would have to convert them to some common denominator, like "number of seconds", add that up and then reconvert to hh:mm:ss format

     

    I don't understand this requirement

    There are some instances where the technician(s) haven't logged off so I need to also need to extract the business hours (08:00 until 17:00, Monday to Friday) within days captured:

    do you mean you need to set the time using the end hour of the working day ?

    I'm a personal Power Bi Trainer I learn something every time I answer a question

    The Golden Rules for Power BI

    1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
    2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
    3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.

     

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

    Duration data can't show as "HH:MM:SS" in Power BI visuals now.

    One workaround, you can format it to "HH:MM:SS" in text and put it on "Tooltip".

    For sum of "HH:MM:SS", hope this post could help you.

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi All, 

     

    Iceythanks for the measure, but I'm getting an error where the technicians have forgotten to log off at the end of the day so my HH:MM:SS is actually D:HH:MM:SS.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi All, 

     

    I've converted the dd:hh:mm:ss to hh:mm:ss with the following:

     

    I'm still getting errors about the time being over 24 hours. Any ideas?

     

    Also, how do you get from a text to a number? 

     

    Regards, 

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

      Sorry to reply late. Please check if this meets your requirements. For details, please check the attached PBIX file.

       

      DD:HH:MM:SS Measure = 
VAR SUMD =
    SUMX (
         'Table (2)',
          INT ( 'Table (2)'[Seconds] / 3600 / 24 ) ---------------------------------------Days
        )
VAR DD =
    IF ( LEN ( SUMD ) > 1, SUMD, CONCATENATE ( 0, SUMD ) )
VAR SUMH =
    SUMX (
        'Table (2)',
        INT (
            (
                'Table (2)'[Seconds]
                    - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24 ----------------Days convert to Seconds
            ) / 3600
        ) ---------------------------------------------------------------------------------Hours
    )
VAR HH =
    IF ( LEN ( SUMH ) > 1, SUMH, CONCATENATE ( 0, SUMH ) )
VAR SUMM =
    SUMX (
        'Table (2)',
        INT (
            (
                'Table (2)'[Seconds]
                    - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24 ----------------Days convert to Seconds
                    - INT (
                        (
                            'Table (2)'[Seconds]
                                - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24
                        ) / 3600
                    ) * 3600 --------------------------------------------------------------Hours convert to Seconds
            ) / 60
        ) ---------------------------------------------------------------------------------Minutes
    )
VAR MM =
    IF ( LEN ( SUMM ) > 1, SUMM, CONCATENATE ( 0, SUMM ) )
VAR SUMS =
    SUMX (
        'Table (2)',
        'Table (2)'[Seconds]
            - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24 ------------------------Days convert to Seconds
            - INT (
                (
                    'Table (2)'[Seconds]
                        - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24
                ) / 3600
            ) * 3600 ----------------------------------------------------------------------Hours convert to Seconds
            - INT (
                (
                    'Table (2)'[Seconds]
                        - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24
                        - INT (
                            (
                                'Table (2)'[Seconds]
                                    - INT ( 'Table (2)'[Seconds] / 3600 / 24 ) * 3600 * 24
                            ) / 3600
                        ) * 3600
                ) / 60
            ) * 60-------------------------------------------------------------------------Minutes convert to Seconds
    )
VAR SS =
    IF ( LEN ( SUMS ) > 1, SUMS, CONCATENATE ( 0, SUMS ) )
RETURN
    CONCATENATE (
        DD,
        CONCATENATE (
            ":",
            CONCATENATE (
                HH,
                CONCATENATE ( ":", CONCATENATE ( MM, CONCATENATE ( ":", SS ) ) )
            )
        )
    )

       

       

      Best Regards,

      Icey

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Icey 

         

        Thanks, that's returning a total but I think I need a calculated column so it can sit in the chart:

         

        Regards,