Scheduling Conflict Report

[keylian031987]

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. 

[keylian031987]

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

[v-jingzhang]

Hi @keylian031987 

Here is my sample file for your reference. 

Jing

[v-jingzhang · Accepted solution]

Hi @keylian031987 

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 null

b. 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.

[keylian031987]

Thanks a lot!

[ribisht17]

@keylian031987 

Not enough explanation and data

Regards,

Ritesh