Need to combine vacation table with calculated shift rotation in same calendar in matrix visual

[coochy5]

Hi all,

Looking for some DAX assistance here.

I've had prior help with this, for which I am appreciative and it's sooo close, I just need to get it over the line.

Had some challenges with uploading pbix here (yes, followed cloud instructions, but still doesn't seem to work), so going with sample data in tables instead.

FACT table - workers with booked days off.  Identifies date and crew (colour) and rotation.  There are many rotations and colours - so only included a small sample set, as it's illustrative of my problem - even though personnel are on the same rotation, they have different crew colours, and would have their shift off days offset from other crews on the same rotation.

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

[Anonymous]

Hi @coochy5 ,

I tried to reproduce your question and there seems to be some problems, how far along are you so far?

Best regards,

Adamk Kong

[coochy5]

Hi there.

Did I provide enough information ?

[coochy5]

Hi.

Thanks for the response.

I'm along, quite far, thanks to previous posts - my last post (https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Looking-for-DAX-assistance-to-combin...) contains a color.pbix, which almost works....

Whilst the days off are offset (the pattern is the correct result), they are not calculating correctly - the shift days off are incorrect....

I need the shift days off to calculate from a static effective date - or some assistance how to set them up appropriately.

(colour format 1 measure needs to be replaced with:

Colour Format1 =

IF (

    [Value_per_day] = "V",

    "#fed68d",

    IF (

        [Value_per_day] = "U",

        "#fed68d",

        IF (

            [IsShiftOffDayMeasure] = 1,

        "#d0aaaa"

 )

        )

    )

I need the shift days off to start from effective dates - in the table attached earlier in this post, but I cannot seem to get them working as expected.

(if you replace the colour format1 measure in the color.pbix from the prior post, you should get this result:

and in the above result, the crew off days should be

Blue - should be 12-17 Dec inclusive.  Next off days +15 from there, 02-Jan - 07-Jan 2025.

Yellow - should be 26-Dec-31-Dec inclusive.  Next off - 16-Jan - 21-Jan 2025.

Red - should be 19-Dec-24-Dec inclusive.  Next off - 09-Jan - 14-Jan 2025.

Please let me know if you need any further info !

[coochy5]

Shift Days off needs to be calculated from :

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

[coochy5]

Created a standard calender to use:

Calendar =

Var

    _startDate = min('FACT'[Date])

Var

    _endDate = max('FACT'[Date])

RETURN

    ADDCOLUMNS(

        CALENDAR(_startDate,_endDate)

        ,"Year", YEAR([Date])

        ,"Month", FORMAT([Date], "mmm")

        ,"Month Sort", MONTH([Date])

        ,"Day", Day([Date])

        ,"Day Of Week", WEEKDAY([Date])

        ,"Day Of Week Name", FORMAT([Date], "ddd")

        ,"Week of Year", WEEKNUM([Date])

        ,"DM", FORMAT([Date], "ddd") & " " & Day([Date]) & "-" & FORMAT([Date], "mmm")

        )

[coochy5]

Measure for shift days:

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 )

[coochy5]

Colour Format2 =

IF (

    [Value_per_day] = "V",

    "#fed68d",

    IF (

        [Value_per_day] = "U",

        "#fed68d",

        IF (

            [IsShiftOffDayMeasure] = 1,

            VAR cc =

                MAX ( 'Fact Crew Worker Time Off V1'[Crew] )

            RETURN

                CALCULATE ( MAX ( 'color'[Color] ), FILTER ( 'color', 'color'[Name] = cc ) )

        )

    )

)

[coochy5]

this is the result that I am hoping to achieve

(only displaying rotation days off (by crew (colour) - needs to also have any vacation days off).

...but ...in production - this is the result I'm actually getting...so is incorrect - suggests all colours are off shift on the same days

Any further assistance is very much appreciated !