PBI - show shift days off in calendar - matrix visualisation

[coochy5]

Hi all,

Relative noob trying to figure this out in PBI.

Trying to create a shift calendar with 8 different shift patterns - all complete days, no specific times to handle thankfully.

I'd need to define a start date for each - and they'd run without a stipulated end date.

Shifts are : (days on/days off)

22/20

22/13

15/6

5/2 

etc, etc.

I've managed to create the visualisation with a matrix to show vacation days, and I'd need to incorporate the shift days off, if possible, and there should be no overlap with vacation/shift days off as vacation is a separately recorded event (vacation table).

Any help is gratefully received !

[Anonymous]

Hi @coochy5 ,

It sounds like you’re working on a complex but interesting project in Power BI. To incorporate shift days off into your calendar visualization, you can follow these steps:

Create a Shift Pattern Table:

• Create a new table in Power BI to define your shift patterns. This table should include columns for the shift name, days on, and days off.

ShiftPattern = DATATABLE(
    "ShiftName", STRING,
    "DaysOn", INTEGER,
    "DaysOff", INTEGER,
    {
        {"Shift1", 22, 20},
        {"Shift2", 22, 13},
        {"Shift3", 15, 6},
        {"Shift4", 5, 2}
        // Add other shifts as needed
    }
)

Create Shift Calendar:

• Create a calculated table to generate the shift calendar based on the start date and the shift patterns. 

ShiftCalendar = 
VAR StartDate = DATE(2024, 1, 1) // Replace with your actual start date
VAR EndDate = DATE(2025, 12, 31) // Replace with your desired end date
VAR DateRange = CALENDAR(StartDate, EndDate)
RETURN
DateRange

Create Shift Name measure:

At last, create a measure to generate the shift status(on/off).

Best Regards,

Stephen Tao

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

[coochy5]

Hi

Many thanks for the response.  I'm looking to incorporate the shifts days off, into an existing matrix, but the shift days off are offset, by crew colours and shift rotation.

This is what my report looks like at the moment. 

Crew colours have different rotations and therefore different shift off days.

Crew	Rotation	Schedule
Magenta	1	15/13
Pink	2	15/13
Blue	1	15/6
Red	2	15/6
Yellow	3	15/6
Purple	1	22/13
Grey	3	22/13
Teal	5	22/13

I'm really struggling to incorporate the days off into a single value I can show on the matrix.  Is it possible ?

This is what I'm ultimately trying to replicate - compiled in C# - and is going away.

Very appreciative of any further assistance or suggestions !

[coochy5]

Hi,

Thank you so much for the info - greatly appreciated.

I would somehow need to incorporate these shift days off, into the same matrix - which is currently handling vacation/unpaid time off from a colour format measure - which hopefully you can see from the image.

The vacation/unpaid piece was relatively simple to figure out - as that's a booked day per individual from a vacation table.

The shift days off, are giving me a headache as they have crew colours, on the same rotations, but the schedule will be different - offset.  Have no idea how to handle that.

I had cased the original data via a sql view, to split out the rotation schedule and crew colour - original, v derived values are :

Example from the 15/6 rotations.

Work Schedule Calendar	Day Breaker	Day Breaker Divides	Work Week Start Day	Biweekly Calculation Start Date	Configurable Calculation Period
Day Rate / Bulker/ Salary - 15/6 Rotation 1 (Blue)	12:00 a.m.		Wednesday	2/22/2017	Month Starting on Day 1
Day Rate / Bulker/ Salary - 15/6 Rotation 3 (Yellow)	12:00 a.m.		Wednesday	2/15/2017	Month Starting on Day 1

Any tips on how I could incorporate the 2 seperate measures into perhaps a single one, as the matrix only accomodates a single value choice ?

Measures:

Colour Format =

IF([Value_per_day] = "U", "#fed68d",

IF([Value_per_day] = "V", "#fed70d"

))

ShiftName =

    VAR CurrentDate = MAX('ShiftCalendar'[Date])

    VAR DaysOn = SELECTEDVALUE(ShiftPattern[DaysOn])

    VAR DaysOff = SELECTEDVALUE(ShiftPattern[DaysOff])

    VAR CycleLength = DaysOn + DaysOff

    VAR CyclePosition = MOD(DATEDIFF(CALCULATE(MIN('ShiftCalendar'[Date]),ALLSELECTED(ShiftCalendar)), CurrentDate, DAY), CycleLength)

    RETURN IF(CyclePosition < DaysOn, "On", "Off")

[lbendlin]

When is each shift cycle starting?

Please provide all cycles, and the desired time range. I assume you don't need that for past dates?