Overtime Request Automated Cloud Flow

Microsoft Forms + Power Automate Flow + Power BI

Created a system for all division staff to request and approve overtime using Microsoft Forms for requests, Power Automate for initiating and routing Teams Approvals, and Power BI to visualize the data for executive staff. Users initiate a Power Automate flow by submitting a request via Forms that creates a new row in a Dataverse table. The Power Automate flow leverages the user’s internal Entra ID Profile, specifically the Job Title field, which the flow uses to calculate the user’s hourly pay and time and a half. The flow also categorizes each request into an appropriate overtime plan depending on the user’s selection on the request form. The flow branches depending on if the user manually inputs a supervisor's email or if they opt to allow Power Automate to leverage Entra ID Profile to automatically identify a respondent’s immediate supervisor and elevated supervisor. The flow branches again depending on if the user is requesting ten or more hours of overtime and additionally branches at each stage depending on the Approval Outcome to either notify the user of approval or rejection of the overtime request. Intermittently, Power Automate updates the corresponding Dataverse row with the Approval Status at each stage of the approval process so Power BI can display real-time data using Direct Query mode. The final product is a Power BI report that allows executive level staff to view real-time data to track progress towards funding and hourly limits applicable to each approved overtime plan.

My Role

Requirements

• Users request overtime via Microsoft Form
• Users must optionally be able to manually input a supervisor’s email for special circumstances with error handling in case of improper data entry.
• The form should have branching logic depending on the user’s county location such as when a user selects a certain county, they are routed to the corresponding overtime plan option.
• Power Automate ingests Forms Responses, creates a Dataverse row, and initiates Approvals
• Approvals must meet the following criteria:
• If a user requests less than ten hours of overtime, an immediate supervisor approval is required.
• If a user requests ten or more hours of overtime, both immediate and elevated supervisor approval is required.
• After five days of no response, an approval is elevated to second level supervisor regardless of overtime amount.
• Automated notifications are sent to the requestor upon approval response.
• Data visualized via Power BI in real-time (Direct Query) to monitor funding limits

Solution Architecture (High Level)

Power BI report

Challenges

• Composing the Power Automate Flow Link for Error handling
• At certain error handling steps, the link to the flow run is sent to the user with instructions to contact the flow owner with the link in case of run failure. This way, the owner can click on the link directly to the failed flow and troubleshoot without having to search through run logs.
Copy

	concat('https://make.gov.powerautomate.us/environments/',workflow()?['tags']?['environmentName'],'/flows/',workflow()?['name'],'/runs/',workflow()?['run']['name'])
• Composing the hourly rate of employees dynamically based on their job title per Entra Id Profile in Power Automate.
Copy

• Setting a “Funding Limit” variable dynamically based on user’s Forms responses in Power Automate.
• When a new Dataverse row is added, the applicable overtime plan, funding limit, and hour limit (all composed using the same logic below) is added to the record. I found this was the simplest way to filter visualizations in Power Bi by funding limit/overtime plan. Doing it this way allowed me to use the “Overtime Plan” field as a slicer in Power BI.

Copy
• To achieve the gauge visual functionality in Power Bi (that visualizes the progress to Fund Limit and Hour Limit), a simple Power BI measure formula was required to calculate each value.
• This measure fetches the max funding limit in the table but is dependent on the visual’s current filtered context. Since an overtime plan slicer (shown above) has a forced selection, this measure will always evaluate to the appropriate funding limit depending on the selected plan in the slicer.
Copy

FundLimitMeasure = MAXX(VALUES(<overtime_table>[Overtime Plan]),MAX(<overtime_table>[Fund Limit]))