Forum Discussion

keylian031987's avatar
keylian031987
Regular Visitor
4 years ago
Solved

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. O...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
keylian031987
Regular Visitor
4 years ago

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 1RFC  
Employee 2RFC4/11/20224/14/2022
Employee 2RFC7/26/20227/29/2022
Employee 2RFC12/23/20221/9/2022
Employee 3RFC7/25/20228/2/2022
Employee 3RFC12/19/20221/4/2022
Employee 4RFC7/25/20228/2/2022
Employee 4RFC12/23/20221/9/2022
Employee 5RFC12/21/20221/5/2022
Employee 5RFC6/13/20226/17/2022
Employee 5RFC7/26/20227/29/2022
Employee 6RFC11/14/202211/29/2022
Employee 7RFC10/31/202211/14/2022
Employee 7RFC4/11/20224/18/2022
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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's avatar
    keylian031987
    Regular Visitor
    4 years ago

    Thanks a lot!