Forum Discussion
Scheduling Conflict Report
Hi
I created a form with Powerapps to collect all 2022 vacations from my division (58 employees). In the form I collect: Employee ID, Employee Name, Employee Backup, Start Date, End Date and Area. One employee can have multiple entries in the year (there are 22 vacations days so days can be distributed through the year). I need to create a report to identify conflict between employees and their backup and I am blocked. Don't know how to do it.
I create a matrix visual as below to show which employees are on which vacation dates. Is this able to meet your need? For example, on 2021/12/23, Employee 2, 3, 4, 5 were on vacation. Employee 1 doesn't have any vacation dates so he is on the row where Vacation Days is blank.
To display all dates, the original start dates and end dates are not enough. I use Power Query Editor to perform the following steps to generate all vacation dates for all employees.
a. Add a custom column "Vacation Days" to have a list of all dates between [Start Date] and [End Date].
try List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0)) otherwise nullb. Expand "Vacation Days" column to New Rows. Change this column to Date type. Then apply this change to Power BI Desktop.
Now you have a table similar to below.
Select a matrix visual, put "Employee" on Columns, "Vacation Days" on Rows. Put Employee on Values and select Count aggregation for it. You will have a matrix as shown in the first image.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
5 Replies
I have this table with Employee, Area, Start Date of Vacation and End Date of Vacation. If you notice Employee 2 and Employee 3 have days that are equal and for me those days are conflict because EMployee 2 is the backup of Employee 3. I need a formula to identify that. Which days are equal between employees.
Employee Area Start Date End Date
Employee 1 RFC Employee 2 RFC 4/11/2022 4/14/2022 Employee 2 RFC 7/26/2022 7/29/2022 Employee 2 RFC 12/23/2022 1/9/2022 Employee 3 RFC 7/25/2022 8/2/2022 Employee 3 RFC 12/19/2022 1/4/2022 Employee 4 RFC 7/25/2022 8/2/2022 Employee 4 RFC 12/23/2022 1/9/2022 Employee 5 RFC 12/21/2022 1/5/2022 Employee 5 RFC 6/13/2022 6/17/2022 Employee 5 RFC 7/26/2022 7/29/2022 Employee 6 RFC 11/14/2022 11/29/2022 Employee 7 RFC 10/31/2022 11/14/2022 Employee 7 RFC 4/11/2022 4/18/2022 I create a matrix visual as below to show which employees are on which vacation dates. Is this able to meet your need? For example, on 2021/12/23, Employee 2, 3, 4, 5 were on vacation. Employee 1 doesn't have any vacation dates so he is on the row where Vacation Days is blank.
To display all dates, the original start dates and end dates are not enough. I use Power Query Editor to perform the following steps to generate all vacation dates for all employees.
a. Add a custom column "Vacation Days" to have a list of all dates between [Start Date] and [End Date].
try List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0)) otherwise nullb. Expand "Vacation Days" column to New Rows. Change this column to Date type. Then apply this change to Power BI Desktop.
Now you have a table similar to below.
Select a matrix visual, put "Employee" on Columns, "Vacation Days" on Rows. Put Employee on Values and select Count aggregation for it. You will have a matrix as shown in the first image.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thanks a lot!
