Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

[coochy5]

Hi all,

Looking for some assistance on a task that is making me question my sanity (of perhaps starting the project).

Replacing an older C# app.

Need to a calender (matrix view) with booked days off combined with rotation days off.

The data source for the vacations is a WorkDay table - sample data attached.

6 distinct rotation schedules & 8 distinct crews (colours).

The crew colours are the differentiator for the days off - 15/6 red would not be off at the same time as 15/6 yellow.

This, cannot be a correct result for example - all red crew would have the same shift days off.

I'm having a challenge getting the rotation schedules and crews (colours) mapping on the matrix correctly - and that's prior to adding the vacation days off - which are also a nightmare.

I do have a version of this report out in production - but that's only with the vacation days - that's working a charm, but I cannot get the shift days off calculated correctly.

This is what I am trying to replicate:

Any help is graciously received !

(adding a link to my sample pbix)

https://drive.google.com/file/d/1hADMpk_o6MVbxUDvjD5cRBv0YZinQY8D/view?usp=drive_link 

Here's my worker data, in a table...

Name

LastName

FirstName

WD_ID

Active

Title

DivisionId

BaseId

Date

TimeType

WorkScheduleCalendar

Rotation

Crew

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

I have this in a calendar table:

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

)

and I have this, to calculate whether the day is a shift on or off day.

IsShiftOffDayMeasure =

VAR RotationDaysOn = VALUE(LEFT(SELECTEDVALUE('Worker_Vacay'[Rotation]), FIND("/", SELECTEDVALUE('Worker_Vacay'[Rotation])) - 1))

VAR RotationDaysOff = VALUE(RIGHT(SELECTEDVALUE('Worker_Vacay'[Rotation]), LEN(SELECTEDVALUE('Worker_Vacay'[Rotation])) - FIND("/", SELECTEDVALUE('Worker_Vacay'[Rotation]))))

VAR StartDate =

    CALCULATE(

        MIN('Worker_Vacay'[Date]),

        ALLEXCEPT('Worker_Vacay', 'Worker_Vacay'[Crew], 'Worker_Vacay'[Name])  

    )

VAR DaysSinceStart = DATEDIFF(StartDate, SELECTEDVALUE('Calendar'[Date]), DAY)

VAR CycleDays = RotationDaysOn + RotationDaysOff

RETURN

IF(

    MOD(DaysSinceStart, CycleDays) >= RotationDaysOn,

    1,  // offday

    0   // workday

)

[coochy5]

Hi.  This is great.  It conceptually works !  Can I ask for some further advice ?  The days on/off schedule varies by for the crew colour and rotations - and the calendars have different commencement dates.  I'm not sure, in the measure you supplied, how the start date of each rest period is calculated.  How should I incorporate the shift patterns - a seperate table referenced in the measure ?

Example data:

Id	Name	DaysOn	DaysOff	Color	EffectiveDate	IsActive	WorkdayName
1	Red	15	6	#ff0000	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)
2	Yellow	15	6	#dede00	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)
3	Blue	15	6	#0000ff	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)
4	Teal	22	13	#008B8B	5/30/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 5 (Teal)
5	Orange	22	13	#ff7f27	5/9/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 2 (Orange)
6	Purple	22	13	#a349a4	5/2/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 1 (Purple)
7	Brown	22	13	#b97a57	5/23/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 4 (Brown)
8	Grey	22	13	#708090	5/16/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 3 (Grey)
9	Magenta	15	13	#f966f9	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)
10	Pink	15	13	#fe7c89	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)

[Anonymous]

Hi, @coochy5 
 

Thank you for your prompt response.

I have imported your new table as the 'color' table:

Next, you can replace the value section of the matrix visual with the following measures:

Colour Format1 = 
IF (
    [Value_per_day] = "V",
    "#fed70d",
    IF (
        [Value_per_day] = "U",
        "#fed68d",
        IF (
            [IsShiftOffDayMeasure] = 1,
            VAR cc =
                MAX ( 'FACT'[Crew] )
            RETURN
                CALCULATE ( MAX ( 'color'[Color] ), FILTER ( 'color', 'color'[Name] = cc ) )
        )
    )
)

Then, adjust its colour settings:

Here are the final results:

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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

[coochy5]

Hi.

Thanks again for your kind assistance.  I think I'm unintentionally misleading here, or not being clear on the requirement.

Crew members are allocated to a shift schedule - eg 15/6, 22/13 - 15 days on, 6 off, 22 days on, 13 off etc.

The crew colour denotes the rotation - so that the rotations are offset.  

Lots of crew will be on a 15/6 shift schedule, but will be on different rotations, so they are not off at the same time.  

I've tried to incorporate your suggestions into my dev report - and it shows all crew colours off at the same time.

Not sure how I am getting this so wrong!

The measures - I believe, are identical - just changed table names.

I've added the same 'Color' table that you have used in your version back to me.

Curiously - I show no shift days off prior to 12-Dec-24, which is also not correct - in my live dataset.  Some crews are already off.

Lastly - on the values in the matrix grid - I only need to display 2 colours - the shift days off would all be the same colour, and vacation/other time off will be the second colour.

Here's my result:

I wonder if it is worthwhile to create a new shift schedule table.  Here's what I think it would look like - with the next set of effective dates for Dec 2024.  (removed some schedules for ease)

Id	Name	DaysOn	DaysOff	EffectiveDate	IsActive	WorkdayName	NewStartDate(ShiftOn)	NextOffDate
1	Red	15	6	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)	4-Dec-24	19-Dec-24
2	Yellow	15	6	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)	11-Dec-24	26-Dec-24
3	Blue	15	6	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)	27-Nov-24	12-Dec-24
4	Magenta	15	13	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)	4-Dec-24	19-Dec-24
5	Pink	15	13	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)	18-Dec-24	2-Jan-25

[coochy5]

Hi,

Were you able to offer any further insights into this ?

I managed to get the 2 time off periods colours sorted out (vacation/other & shift days off), but am unable to get the shift days off correctly represented in the offset form.

All shift days off seem to follow the same pattern, which is not correct, and not the result you achieved.

This....is not the correct result...(from my prod instance)

This particular group, should look like this, for the date periods...

Any further assistance is greatly appreciated !

Am hoping this latest attempt to share the .pbix is successful.

color_test_calendar.pbix

[Anonymous]

Hi, @coochy5 

Firstly, regarding the PBIX file you shared, I am currently unable to access it due to privacy protocol issues. However, I have reviewed your previous questions and obtained the PBIX file related to the relevant responses.

Therefore, if your data requirements have been updated, please refer to the following link:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

When uploading a file, please be careful to delete sensitive information.

Based on my understanding of your question, you require the same rest period for the same Crew. Please replace the DAX with the following content:

IsShiftOffDayMeasure = 
VAR Rotation =
    SELECTEDVALUE ( 'FACT'[Rotation] )
VAR RotationDaysOn =
    VALUE ( LEFT ( Rotation, FIND ( "/", Rotation ) - 1 ) )
VAR RotationDaysOff =
    VALUE ( RIGHT ( Rotation, LEN ( Rotation ) - FIND ( "/", Rotation ) ) )
VAR StartDate =
    CALCULATE ( MIN ( 'FACT'[Date] ), ALLEXCEPT ( 'FACT', 'FACT'[Crew] ) )
VAR DaysSinceStart =
    DATEDIFF ( StartDate, SELECTEDVALUE ( 'Calendar'[Date] ), DAY )
VAR CycleDays = RotationDaysOn + RotationDaysOff
RETURN
    IF ( MOD ( DaysSinceStart, CycleDays ) >= RotationDaysOn, 1, 0 )

Here are the final results:

If I have misunderstood your requirements, I would appreciate your prompt correction, along with a diagram to illustrate your needs.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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