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 ???

[jgeddes]

Here is a pbix file that hopefully gets you pointed in the right direction. It will not be the final answer for you, but should get you started.

EDIT: Uploaded edited pbix to correct error in measure.

[coochy5]

Hi,

Thanks for the response, and forgive my tardiness in responding back.

I see what you've achieved in your sample - never quite works out for me - really unsure why.

Only real difference I see between your version and mine, I how the calendar is created (mine is based on a min and max of a worker vacation data set) - I had applied the same conditional formatting to the IsOn measure.

This is what I end up with.

Can I supply some test data for you ?  

[jgeddes]

I used the data you posted and amended the pbix. Hope this helps.

[coochy5]

ok, ok, ok !  that's fantastic....and almost there - can I go for one more ask ???

on the matrix itself, there's only 2 colours in play - for vacation/other, and shift days off - I don't need the shift days off in the actual crew colour.....

I thought it's handled in the 'isOff' measure - so I replaced "black" with a hex colour, but don't easily interpret where to change the shift colour - is that possible ???

Colours I need are :

vacay (booked day off)  "#fed70d"

IsShiftOffDay "#FFB6C1"

[jgeddes · Accepted solution]

If you change the isOff measure to...

isOff = 
var _employee = 
SELECTEDVALUE(vacationTable[Name])
var _shiftEffectDate = 
MINX(shift_table, shift_table[EffectiveDate])
var _modValue = 
MINX(shift_table, shift_table[DaysOn]) + MINX(shift_table, shift_table[DaysOff])
var _comparisonValue = 
MINX(shift_table, shift_table[DaysOn])
var _testValue =
Value(SELECTEDVALUE(dimDate[Date]) - _shiftEffectDate)
var _mod =
MOD(_testValue, _modValue)
var _shiftName = 
"#FFB6C1"//MINX(shift_table, shift_table[Name])
var _testDate = 
SELECTEDVALUE(dimDate[Date])
var _offCount = 
COUNTROWS(FILTER(vacationTable, vacationTable[Name] = _employee && vacationTable[Date] = _testDate))
Return
IF(
    _testValue >= 0,
    IF(_mod>=_comparisonValue,_shiftName,IF(_offCount>0,"#fed70d" ,"")),
    ""
)

You will end up with...

[coochy5]

Gosh, I've broken it again !

I've tried to incorporate with prod data - so have tinkered with the isOff and isOn measures (just substituting the table names), and added to the dimDate table (just more columns) - and I think I've replicated all....but end result is that whilst vacations plot correctly, all shift schedules seem to start and end of the same dates.

Any idea what's going on here ?

In the sample data set - shift off days should be:

Red off shift 12/19-12/24

Blue off shift 01/02 - 01/07

Yellow off shift - 12/26-12/31

[jgeddes]

Make sure the relationships are still valid for the new table(s) you are using.

[coochy5]

that was the issue !

Really - I can't thank you enough !

Looks great !

[coochy5]

fantastic !

thank you very much !

Looks like what I need exactly !

I'll run through the changes to prod let you know how it goes !

Many thanks again for your assistance !

[coochy5]

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 data I've used to achieve the mock up:

Vacation days:

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

[coochy5]

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)