Calculate with Date and Time (Payroll Shift hours)

[cocoloco79]

Hi everyone,

I like to work out the utilisation of time that can be spend in one work area given the day has 24 hours.

examaple:

01/01/22 

Crew members = 5

Shift 1 = 8 hours each

Shift 2 = 10 hours each

day utilisation would be  8+10= 18 hours which is 75%

Instead of summing up the hours of each crew member I like to look at a time span and see how many hours got utilsed.

Has anyone done a calc like this before?

I have attached a pbix. https://1drv.ms/u/s!AnImJLdOf4nUgy-YLhZCbbkSkqAT?e=I9JPW5 

Thank you very much in advance!

[Anonymous · Accepted solution]

Hi @cocoloco79 ,

Please check the formula.

Measure =
VAR min_start =
    CALCULATE (
        MIN ( Payroll[DateTimeStart] ),
        FILTER (
            ALLSELECTED ( Payroll ),
            Payroll[WorkareaName] = SELECTEDVALUE ( Payroll[WorkareaName] )
                && Payroll[DateStart] = SELECTEDVALUE ( Payroll[DateStart] )
        )
    )
VAR max_end =
    CALCULATE (
        MAX ( Payroll[DateTimeStop] ),
        FILTER (
            ALLSELECTED ( Payroll ),
            Payroll[WorkareaName] = SELECTEDVALUE ( Payroll[WorkareaName] )
                && Payroll[datestop] = SELECTEDVALUE ( Payroll[datestop] )
        )
    )
RETURN
    DATEDIFF ( min_start, max_end, MINUTE ) / 60

Best Regards,

Jay

[TomMartens]

Hey @cocoloco79 ,

please provide the expected result of the mentioned calculation based on the data you provided, next to the calculation also explain how you want to use this calculation in your data visualization.

Regards,

Tom

[cocoloco79]

Hi @TomMartens 

Please refer to the attached screenshot. I like to calculate the difference between the earliest DateTimeStart and the latest DateTimeStop. This would show the hours utilised in one work area by day. I would then visualise the hours as a % where 24Hrs is 100%.

Backround to this is that; I like to show machine usage for each day while I only have employee login times and no access to the machine itself. Each work area is setup with one packing machine.