Time on-site

[Mrs_B]

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	StartDateTime	EndDateTime	Item	Assignee	Duration On-Site	Total Time On-Site
1	26/11/2019 12:48	26/11/2019 17:27	On-site time	A	4.65	04:39:15
1	27/11/2019 08:19	27/11/2019 17:18	On-site time	A	8.98	08:59:40
1	28/11/2019 08:00	28/11/2019 16:56	On-site time	A	8.93	08:56:45
1	29/11/2019 09:45	29/11/2019 17:25	On-site time	A	7.67	07: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 ID	StartDateTime	EndDateTime	On-site time	Assignee
2	24/09/2019 08:43	05/11/2019 12:11	On-site time	B
2	17/10/2019 10:41	17/10/2019 10:41	On-site time	C
2	17/10/2019 08:00	17/10/2019 09:00	On-site time	C
2	14/11/2019 17:26	21/11/2019 16:54	On-site time	B
2	16/08/2019 08:18	16/08/2019 08:18	On-site time	B

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,

[Mrs_B]

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]

Hi @Mrs_B ,

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.

[Mrs_B]

Hi @Icey 

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

Regards, 

[Icey]

Hi @Mrs_B ,

What about the calculated column "DD:HH:MM:SS Column" in the attached PBIX file in my last reply? Can it work?

DD:HH:MM:SS Column = 
VAR D =
    INT ( 'Table (2)'[Seconds] / 3600 / 24 )
VAR DD =
    IF ( LEN ( D ) > 1, D, CONCATENATE ( 0, D ) )
VAR H =
    INT ( ( 'Table (2)'[Seconds] - D * 3600 * 24 ) / 3600 )
VAR HH =
    IF ( LEN ( H ) > 1, H, CONCATENATE ( 0, H ) )
VAR M =
    INT ( ( 'Table (2)'[Seconds] - D * 3600 * 24 - HH * 3600 ) / 60 )
VAR MM =
    IF ( LEN ( M ) > 1, M, CONCATENATE ( 0, M ) )
VAR S = 'Table (2)'[Seconds] - D * 3600 * 24 - HH * 3600 - MM * 60
VAR SS =
    IF ( LEN ( S ) > 1, S, CONCATENATE ( 0, S ) )
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.

[Mrs_B]

Hi @Icey 

Firstly, thank you for your help, its a problem I've been stuck on for some time. 

That generated a column but its text so shows as a count in the chart. When I try to convert it to a number I receive the following error:

 

Is it possible to convert seconds into hours and minutes with a decimal? For example, 1 hour and 30 minutes would show as 1.30.

Kindest Regards, 

[Icey]

Hi @Mrs_B ,

Power BI doesn't support to show data over 24 hours as HH:MM:SS or Duration type.

You can create your visual using "Seconds" column and then put "DD:HH:MM:SS" column on "Tooltips" field.

Best Regards,

Icey

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

[Mrs_B]

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.

[Icey]

Hi @Mrs_B ,

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.

[kentyler]

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.