Forum Discussion
Help calculating work hours duration based on status in and status out entries in Power Query
The simplest way to do this would be to first create a calculated column (DAX is better than M for this one), with the column expression below (gives the time in hours at the status on the current row by finding the next status update (in or out)). Replace Times with your actual table name.
Time At Status =
VAR thisemployee = Times[EmployeeID]
VAR thistime = Times[Entry created at]
VAR nexttime =
CALCULATE (
MIN ( Times[Entry created at] ),
ALLEXCEPT ( Times, Times[EmployeeID] ),
Times[Entry created at] > thistime
)
RETURN
IF ( NOT ( ISBLANK ( nexttime ) ), ( nexttime - thistime ) * 24 )You can then use a simple measure to just count the "in" or "out" time to get your result.
In total = CALCULATE(SUM(Times[Time At Status]), Times[Status] = "In")Pat
Microsoft EmployeeThe simplest way to do this would be to first create a calculated column (DAX is better than M for this one), with the column expression below (gives the time in hours at the status on the current row by finding the next status update (in or out)). Replace Times with your actual table name.
Time At Status =
VAR thisemployee = Times[EmployeeID]
VAR thistime = Times[Entry created at]
VAR nexttime =
CALCULATE (
MIN ( Times[Entry created at] ),
ALLEXCEPT ( Times, Times[EmployeeID] ),
Times[Entry created at] > thistime
)
RETURN
IF ( NOT ( ISBLANK ( nexttime ) ), ( nexttime - thistime ) * 24 )
You can then use a simple measure to just count the "in" or "out" time to get your result.
Pat
When I do this I get the following error code: A single value for column 'EmployeeID' in table 'Times' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Do you know how to resolve?