Gantt - Martix - Rostering Work Patterns

[psh_unreal]

Hi Power BI Community,

I am new to Power BI and have been tasked with creating a Rostering Dashboard within Power BI. I have used the Matrix approach and have measures configured along with conditional formatting to show me a gantt style visual. I am having some troubles with a particular part of the visual and I figured I would reach out to the experts.

Some context:
The use case for this dashboard is to identify which staff are available for shifts.

In our system, staff need to log “unavailabilites” when they are unable to work. We currently have two types of unavailabilities for staff – daily and weekly:

Daily Unavailabilities – this is when a staff member has, as an example, logged leave for one day. I have managed to pull this off and can get this to work. Sample screenshot of the data & matrix below:

Daily Matrix Visual

Daily Data

Weekly Unavailabilites – these are typically used when a staff member has a work pattern. For example, they may be part time and only work from 9am to 5pm, but on a Monday, Wednesday and Friday only. This is where I run into trouble. Sample screenshot of data below – the Start and End dates will typically span a period of weeks or months. The days they can work during this period are identified with “0”.

Work pattern Data

The matrix I have currently looks at the Start and End dates only and marks the Employee as unavailable for the entire period, irrespecitive of what day it is. What I need it to do is only make the Employee unavailable on the days marked “1” that are within the Start and End date periods and I am a bit stuck on how to approach this. 

Any help or advice will be greatly appreciated.

Kind Regards,

Daniel 

[v-yinliw-msft · Accepted solution]

Hi psh_unreal,

You can try this method:

New a calendar table:

Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))

Then calculate the weekday.

New a column:

Weekday = WEEKDAY('Date'[Date],2)

Then new a measure in the sample table:

LeaveDays =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Date'[Weekday] ) = 1
        && CALCULATE (
            MAX ( 'Table'[Monday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Mon leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 2
        && CALCULATE (
            MAX ( 'Table'[Tuesday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Tue leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 3
        && CALCULATE (
            MAX ( 'Table'[Wednesday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Wed leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 4
        && CALCULATE (
            MAX ( 'Table'[Thursday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Thu leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 5
        && CALCULATE (
            MAX ( 'Table'[Friday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Fri leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 6
        && CALCULATE (
            MAX ( 'Table'[Saturday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Sat leave",
    SELECTEDVALUE ( 'Date'[Weekday] ) = 7
        && CALCULATE (
            MAX ( 'Table'[Sunday] ) = 1,
            FILTER ( 'Table', 'Table'[EmployeeID] )
        ), "Sun leave"
)

You can use a slicer and the result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@psh_unreal , refer if this can help

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs