Need to create a shift (days off) calendar in matrix visualisation - overlaid with vacations

[coochy5]

Hi there,

Hoping for some assistance, as I've come completely unstuck with this, and not even sure if best handled within Power Query, or in DAX.

Why I don't know which it should be - trying to map the result on a calendar (matrix visualisation), in addition to actual crew vacation days.

I need to create a calendar, showing shift off days.  Many shifts, many with the same rotation - are differentiated by crew (colours), and each crew colour, even if on the same rotation, would have a different start date, to not overlap.

(on the visualisation - need a multi-select of the crew colours also)

Have tried this...

ShiftPattern = DATATABLE(

    "Crew", STRING,

    "DaysOn", INTEGER,

    "DaysOff", INTEGER,

    {

        {"Red", 15, 6},

        {"Yellow", 15, 6},

        {"Blue", 15, 6},

        {"Teal", 22, 13},

        {"Orange", 22, 13},

        {"Purple", 22, 13},

        {"Brown", 22, 13},

        {"Grey", 22, 13},

        {"Magenta", 15, 13},

        {"Pink", 15, 13}

        }

Here's the data I'm looking at

Id	Name	DaysOn	DaysOff	EffectiveDate	IsActive	WorkdayName
1	Red	15	6	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)
2	Yellow	15	6	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)
3	Blue	15	6	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)
4	Teal	22	13	5/30/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 5 (Teal)
5	Orange	22	13	5/9/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 2 (Orange)
6	Purple	22	13	5/2/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 1 (Purple)
7	Brown	22	13	5/23/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 4 (Brown)
8	Grey	22	13	5/16/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 3 (Grey)
9	Magenta	15	13	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)
10	Pink	15	13	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)

This result, is close....but the shift days off are not correct/mapping correctly.

(As you'll see....Yellow crew - should all be off shift on the same dates, all Red crew off at the same time (as all other Red crew), but Red would not be off at the same time as Yellow.

(measure for this pbix is 

Colour Format =

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

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

IF([IsShiftOffDayMeasure] = 1, "#FFB6C1"

)))

Still unable to upload pbix....

Hoping someone can help ???

[lbendlin]

[coochy5]

Hi there.

Thanks for this.  I thought I was finally following the concept, but not working out for me....

Looked at your pbix - and tried to incorporate into mine...with the two meaures - don't get the same result....

This is what I get...still sample data...

I've added (and changed the measures - and the conditional formatting)

Couple of issues here :

Calendar - dates whilst no real need to show historical dates at all....maybe last 30 days or so - but future dates need to incorporate latest planned vacation day - may be months in the future, not years.

(and of course, the visualisation needs to incorporate the vacation days....I'll attach a table)

Ultimate result I'm trying to achieve is a named employee crew schedule - combined with the employee vacation dates... 

This is what I've been using:

Value_per_day =

LEFT (

    MAXX (

        FILTER (

            ALLSELECTED ( 'Worker_Vacay' ),

            'Worker_Vacay'[Date] = MAX ( 'Calendar'[Date] )

                && 'Worker_Vacay'[Name] = MAX ( 'Worker_Vacay'[Name] )

        ),

        'Worker_Vacay'[TimeType]

    ),

    1

)

Colour Format =

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

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

IF([IsShiftOffDayMeasure] = 1, "#FFB6C1"

)))

[coochy5]

Worker_Vacay table:

NameLastNameFirstNameWD_IDActiveTitleDivisionIdBaseIdDateTimeTypeWorkScheduleCalendarRotationCrew

Owen Money

Money

Owen

216

1

Supervisor 3

EA

SPO

1/19/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

1

Supervisor 2

MO

MOO

11/28/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

1

Supervisor 2

MO

MOO

11/29/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

1

Supervisor 2

MO

MOO

11/30/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

1

Supervisor 2

MO

MOO

1/21/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

1

Supervisor 2

MO

MOO

4/18/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

12/29/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

11/25/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

11/26/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

12/30/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

11/27/2004

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

1

Operator 4

NM

HIT

12/31/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Will Rodgers

Rodgers

Will

9961

1

Operator Frac Blender 4

MR

IDK

12/29/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Will Rodgers

Rodgers

Will

9961

1

Operator Frac Blender 4

MR

IDK

12/10/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Will Rodgers

Rodgers

Will

9961

1

Operator Frac Blender 4

MR

IDK

12/11/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Kathy Finger

Finger

Kathy

2223

1

Assistant Executive

CO

CAT

12/24/2024

Vacation (Days)

Day Rate / Bulker - 15/6 Rotation 4 (Purple)

15/6

Purple

Kathy Finger

Finger

Kathy

2223

1

Assistant Executive

CO

CAT

12/30/2024

Vacation (Days)

Day Rate / Bulker - 15/6 Rotation 4 (Purple)

15/6

Purple

Sean Potts

Potts

Sean

10341

1

Tech II

SA

POD

12/17/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

1

Tech II

SA

POD

12/18/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

1

Tech II

SA

POD

1/2/2025

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

1

Tech II

SA

POD

12/16/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Brianna Williams

Williams

Brianna

14

1

Supervisor 1

EA

SPO

11/29/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 3 (Yellow)

15/6

Yellow

Nicky Fasterhaus

Fasterhaus

Nicky

1265

1

Operator 1

GP

CTC

1/1/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 3 (Yellow)

15/6

Yellow

[lbendlin]

What happens after they took a vacation?  does their rotation schedule reset?

[coochy5]

Hi.  Nothing would happen. 

Presumption is they stay on the same schedule until they leave or change jobs.

Also presumed, is that they would not record vacation days if the shift is off...

If the days do turn out to be the same...doesn't really matter how it's shown, as they would still be off...so displaying it as vacation or shift off doesn't matter.  It's an implied available/onshift calendar unless a vacation day is booked, or the entire shift is off.

[lbendlin]

You would use the shift calendar (which is based on WorkdayName/group) and then EXCEPT it with the vacation table for that particular individual.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[coochy5]

Hi.

Thanks again for the response.

Just for clarity - I hope :

Vacations are recorded events (dates) - in a worker vacation table.

Shift days off - need to be calculated.

Trying to combine both of these into a matrix visualisation.

Have mocked up the result I'm looking for in excel - sample names/titles etc - real dates, real schedules.

This is the vacation data I used....

Name	Title	BaseId	DivisionId	Date	TimeType	WorkScheduleCalendar	Rotation	Crew
Adam Frohlich	Supervisor  1	GPC	CT	1/15/2025	Unpaid Time Off (Days)	15/6 Rotation 2 (Red)	15/6	Red
Bob Jensen	Supervisor	SPO	EA	12/21/2024	Vacation (Days)	15/6 Rotation 3 (Yellow)	15/6	Yellow
Bob Jensen	Supervisor	SPO	EA	12/22/2024	Vacation (Days)	15/6 Rotation 3 (Yellow)	15/6	Yellow
Brad Jensen	Supervisor	CAT	CO	12/31/2024	Vacation (Days)	15/6 Rotation 1 (Blue)	15/6	Blue
Brett Pitts	Operator 3	IDK	MR	12/18/2024	Unpaid Time Off (Days)	15/6 Rotation 2 (Red)	15/6	Red
Brian Eales	Supervisor 3	GPC	CT	12/29/2024	Unpaid Time Off (Days)	15/6 Rotation 1 (Blue)	15/6	Blue
Cletus Toms	Operator 1	GPC	FR	1/8/2025	Unpaid Time Off	15/6 Rotation 1 (Blue)	15/6	Blue
Cletus Toms	Operator 1	GPC	FR	1/14/2025	Unpaid Time Off	15/6 Rotation 1 (Blue)	15/6	Blue
Drake Blinder	Operator 4	MOO	MO	12/18/2024	Unpaid Time Off	15/6 Rotation 3 (Yellow)	15/6	Yellow
Drake Blinder	Operator 4	MOO	MO	12/20/2024	Unpaid Time Off	15/6 Rotation 3 (Yellow)	15/6	Yellow
Dustin Hoff Man	Supervisor 2	RDC	FR	1/2/2025	Unpaid Time Off (Days)	15/13 Rotation 1 (Magenta)	15/13	Magenta
Enid Blyton	Supervisor 1	RDC	FR	12/23/2024	Unpaid Time Off (Days)	15/13 Rotation 2 (Pink)	15/13	Pink
Enid Blyton	Supervisor 1	RDC	FR	12/27/2024	Unpaid Time Off (Days)	15/13 Rotation 2 (Pink)	15/13	Pink
Eranda Samuel	Operator 3	HNT	FR	12/28/2024	Unpaid Time Off	15/6 Rotation 2 (Red)	15/6	Red
Eranda Samuel	Operator 3	HNT	FR	12/29/2024	Unpaid Time Off	15/6 Rotation 2 (Red)	15/6	Red
Fred Bloggs	Operator 1	POD	SA	12/25/2024	Parental (Days)	15/6 Rotation 2 (Red)	15/6	Red
Fred Bloggs	Operator 1	POD	SA	12/27/2024	Parental (Days)	15/6 Rotation 2 (Red)	15/6	Red
Mowgli General	Operator 2	GPC	CT	12/21/2024	Unpaid Time Off	22/13 Rotation 5 (Teal)	22/13	Teal
Mowgli General	Operator 2	GPC	CT	12/22/2024	Unpaid Time Off	22/13 Rotation 5 (Teal)	22/13	Teal
Plumber Bob	Supervisor 1	RDC	FR	12/29/2024	Unpaid Time Off (Days)	15/6 Rotation 2 (Red)	15/6	Red
Robert Beales	Supervisor 3	GPC	CT	12/18/2024	Unpaid Time Off (Days)	15/6 Rotation 1 (Blue)	15/6	Blue
Robert Beales	Supervisor 3	GPC	CT	12/23/2024	Unpaid Time Off (Days)	15/6 Rotation 1 (Blue)	15/6	Blue
Rough Apprentice	Supervisor 2	RDC	CT	12/20/2024	Unpaid Time Off (Days)	15/6 Rotation 3 (Yellow)	15/6	Yellow
Sean Ewes	Operator 2	HIT	NM	12/20/2024	Unpaid Time Off	15/6 Rotation 3 (Yellow)	15/6	Yellow
Sean Ewes	Operator 2	HIT	NM	12/22/2024	Unpaid Time Off	15/6 Rotation 3 (Yellow)	15/6	Yellow

The shift schedules would be generated from this data:

Here's the data I'm looking at

Id	Name	DaysOn	DaysOff	EffectiveDate	IsActive	WorkdayName
1	Red	15	6	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)
2	Yellow	15	6	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)
3	Blue	15	6	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)
4	Teal	22	13	5/30/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 5 (Teal)
5	Orange	22	13	5/9/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 2 (Orange)
6	Purple	22	13	5/2/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 1 (Purple)
7	Brown	22	13	5/23/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 4 (Brown)
8	Grey	22	13	5/16/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 3 (Grey)
9	Magenta	15	13	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)
10	Pink	15	13	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)

[lbendlin]

We are missing the third table - the employees. There can be employees that are not in the vacation list.

We are also looking at a SCD scenario - how should employees changing teams/shifts  be handled?

[coochy5]

Hi.  No employee table.  Only a worker time off table.  It's really a confirmation check that they have the resources available, from other shift patterns - so they are really double-checking if someone is booked off prior to committing.  

[lbendlin]

so they are really double-checking if someone is booked off prior to committing.

I don't know what that means. Can you please elaborate?

[coochy5]

Hi.

It is a field operations vacation/shift calendar.

So, not all employees will need to be on it.

If they want a particular person (position/job title) committed to a field job - they check the  calender to see if they have time booked off during the job phase - a supervisor for example - and if they have - they will look to see if there is another supervisor available on the same shift rotation (assuming the entire rotation is not off shift).   Does that help ?

[coochy5]

Any further assistance is greatly appreciated - I'm finding this a very tricky challenge !