Power Platform Community Forum Thread Details

Hi,

I have a list that contains details of over 400 employees. This includes start and leave date for those that have left and the "employment end date" column is blank for those still with us. Every month, I need to run a frequency report of incidents and accidents. this needs to be accompanies by the same report for the same month in previous year, or i may need to run ad hoc reports on a given month / week etc.

So what I need the automate flow to do is for each of the employee records, determine how many hours each employee worked in a given period, taking into account whether they started or left in the given period.

Any help muchos gracias.

Categories:

Building flows

If I understand this correctly, I think the trick that makes this simple is to "clamp" each employee's dates to the period you're reporting on. Do that, and starters, leavers, and full-month folks all fall out of the same logic.

Set the period as two variables so you can reuse the Flow for this month, last year's month, or any ad hoc range:

periodStart
periodEnd

Pull the people with "Get Items". Be careful with 400+ records it only returns the first 100 by default, so go to "Settings", turn "Pagination" on, and set the threshold to 5000. More on that here.

Then loop the records and work out each person's effective window.

Effective start (later of their start and the period start):

if(greater(ticks(item()?['StartDate']), ticks(variables('periodStart'))), item()?['StartDate'], variables('periodStart'))

Effective end (a blank end date means still employed, so treat it as the period end, then take the earlier of the two):

if(less(ticks(if(empty(item()?['EmploymentEndDate']), variables('periodEnd'), item()?['EmploymentEndDate'])), ticks(variables('periodEnd'))), if(empty(item()?['EmploymentEndDate']), variables('periodEnd'), item()?['EmploymentEndDate']), variables('periodEnd'))

If someone didn't overlap the period, ticks(effectiveEnd) will be less than ticks(effectiveStart). Log 0 and move on.

For the hours, you likely want working days. Walk the window a day at a time with a "Do Until" and only count a day when "dayOfWeek" isn't 0 (Sunday) or 6 (Saturday). That's also where you'd skip public holidays from a lookup list. Then: hours = working days x hours per day.

You can find the "dayOfWeek" reference here.

Two quick gotchas:

"ticks" turns a date into a number, which is what makes those "later of / earlier of" comparisons reliable: https://manueltgomes.com/reference/powerautomate-function-reference/power-automate-ticks-function/
Always use the question mark before the column, item()?['EmploymentEndDate'], so a blank comes back as null instead of erroring.

For "same month last year", just feed the same Flow a periodStart and periodEnd shifted back a year.

Sorry if this is a bit more detailed than expected 😃. Trying to make things easy but if something is not clear let me know and I'll detail further.