Dax assistance needed - shift/rotation calendar needed in conjunction with a vacation calender.

[coochy5]

Hi all,

Already posted a q about this - in the general group, but I think it's died.

I've created a pbix with a matrix view that needs to show vacation days and shift days off.

Multiple shifts involved, various (historical) start dates.

This is what I'm trying to recreate - was built in C#, but is dying:

Each worker has a ‘WorkScheduleCalender’ string on their HR record, which I’ve delineated out into a ‘Rotation’ and ‘Crew’ field in a sql view, for slicers.  (Not sure that is helping, or hindering me !)

I’ve got as far as creating a version, that operates well on just the vacation days off basis – I really have no idea how to incorporate the shift off days, into the same matrix visual.

This is as far as I have been able to go with just the vacation/day off calendar - not yet able to incorporate shift days off.

Assumptions.  Shift rotations don't overlap.  Employees don't book vacations during shift off days.

Sample of the shift rotations...

Work Schedule Calendar	Work Week Start Day	Biweekly Calculation Start Date	Configurable Calculation Period
Day Rate - 10/4 Rotation 2	Tuesday	2/21/2017	Month Starting on Day 1
Day Rate / Bulker / Hourly - 22/13 Rotation 1 (Purple)	Wednesday	5/2/2018	Month Starting on Day 1
Day Rate / Bulker / Hourly - 22/13 Rotation 2 (Orange)	Wednesday	5/9/2018	Month Starting on Day 1
Day Rate / Bulker / Hourly - 22/13 Rotation 3 (Grey)	Wednesday	5/16/2018	Month Starting on Day 1
Day Rate / Bulker / Hourly - 22/13 Rotation 4 (Brown)	Wednesday	5/23/2018	Month Starting on Day 1
Day Rate / Bulker / Hourly - 22/13 Rotation 5 (Teal)	Wednesday	5/30/2018	Month Starting on Day 1
Day Rate / Bulker /Salary - 15/6 Rotation 2 (Red)	Wednesday	2/8/2017	Month Starting on Day 1
Day Rate / Bulker/ Salary - 15/6 Rotation 1 (Blue)	Wednesday	2/22/2017	Month Starting on Day 1
Day Rate / Bulker/ Salary - 15/6 Rotation 3 (Yellow)	Wednesday	2/15/2017	Month Starting on Day 1
Day Rate / Salary  - 10/4 Rotation 1	Tuesday	2/14/2017	Month Starting on Day 1
Salary - 10/4 Rotation 1	Sunday		Month Starting on Day 1
Salary - 10/4 Rotation 2	Sunday		Month Starting on Day 1
Salary - 15/13 Rotation 1 (Magenta)	Wednesday	4/1/2020	Month Starting on Day 1
Salary - 15/13 Rotation 2 (Pink)	Wednesday	4/15/2020	Month Starting on Day 1
Salary - 15/13 Rotation 3	Wednesday	4/1/2020	Month Starting on Day 1
Salary - 15/13 Rotation 4	Wednesday	4/1/2020	Month Starting on Day 1
Hourly - 8/6 Rotation 1 (10hr)	Wednesday	2/15/2017	Month Starting on Day 1
Hourly - 8/6 Rotation 1 Tu-Mo (10hr)	Tuesday	2/14/2017	Month Starting on Day 1
Hourly - 8/6 Rotation 2 (10hr)	Wednesday	2/22/2017	Month Starting on Day 1
Hourly - 8/6 Rotation 2 Tu-Mo (10hr)	Tuesday	2/21/2017	Month Starting on Day 1

Any assistance or recommendations for an alternate method of replicating this, is greatly appreciated !!!

Sample data

(uploading again)

https://1drv.ms/u/c/7e54286728b81af7/EfllLfwmbrtDrt1BDkcmSJcBWt8iP5dvXyvD-RNhTErYkg?e=lFdMbH 

[Anonymous]

Hi @coochy5, 

Please verify if the following is helpful.

Preparation of the table：

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2024,1,1), DATE(2024,12,31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Weekday", WEEKDAY([Date],2)
)

Measure:

ShiftStatus = 
VAR StartDate = MAX('Shift Schedule'[Shift Start Date])  
VAR CurrentDate = MAX('Calendar'[Date])  
VAR WorkDays = MAX('Shift Schedule'[Work Days])  
VAR OffDays = MAX('Shift Schedule'[Off Days])  
VAR TotalDays = WorkDays + OffDays  
VAR DaysPassed = DATEDIFF(StartDate, CurrentDate, DAY)  
VAR CyclePosition = MOD(DaysPassed, TotalDays)  
RETURN 
IF(CyclePosition < WorkDays, "workday" ,"offday")

VacationStatus = 
IF(
    MAX('Calendar'[Date]) >= MAX('Vacation'[Vacation Start Date]) && 
    MAX('Calendar'[Date]) <= MAX('Vacation'[Vacation End Date]),
    "Vacation", 
    BLANK()
)

FinalStatus = 
IF(
    NOT(ISBLANK(('Vacation'[VacationStatus]))),
    'Vacation'[VacationStatus],
    'Shift Schedule'[ShiftStatus]
)

Result for your reference:

Best regards,

Joyce

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.  It is very helpful, and gets me much closer to what I'm trying to get - but not quite there.  I get it....don't get it !

I tried to use your suggestion - muddled up with my own - and confused myself and the result.

I've uploaded a newer version of the pbix - sample data.  I've added all of the shift schedules, and their start dates - I guess I'm mainly confused about the vacation piece - it's a one row per event data set - so not sure if I should set the vacation start and end to be the same.  

Sample set only includes workers on the same rotation, but different crews (colours) - and the crew colour would have a different/offset start day on/off.

https://drive.google.com/file/d/1OoPk-sxRww6XIrs2zbSaMUwz8BtZ8Wlz/view?usp=sharing 

Appreciate any further insight you could provide !

Thanks in advance !

[Anonymous]

Hi @coochy5 , 

I am currently unable to access your PBIX file.

Could you please try the following method instead?

Do not include sensitive information or anything not related to the issue or question.

[coochy5]

Hi there.

Sorry to reach out directly, were you able to view my pbix ?  Seems to be shared ?

[Anonymous]

Hi @coochy5 ，

Please check the following measure:

IsShiftOffDayMeasure = 
VAR RotationDaysOn = VALUE(LEFT(SELECTEDVALUE('Fact Crew Worker Time Off V1 sample'[Rotation]), FIND("/", SELECTEDVALUE('Fact Crew Worker Time Off V1 sample'[Rotation])) - 1))
VAR RotationDaysOff = VALUE(RIGHT(SELECTEDVALUE('Fact Crew Worker Time Off V1 sample'[Rotation]), LEN(SELECTEDVALUE('Fact Crew Worker Time Off V1 sample'[Rotation])) - FIND("/", SELECTEDVALUE('Fact Crew Worker Time Off V1 sample'[Rotation]))))
VAR StartDate = 
    CALCULATE(
        MIN('Fact Crew Worker Time Off V1 sample'[Date]), 
        ALLEXCEPT('Fact Crew Worker Time Off V1 sample', 'Fact Crew Worker Time Off V1 sample'[Crew], 'Fact Crew Worker Time Off V1 sample'[Name])  
    )
VAR DaysSinceStart = DATEDIFF(StartDate, SELECTEDVALUE('Calendar'[Date]), DAY)
VAR CycleDays = RotationDaysOn + RotationDaysOff
RETURN
IF(
    MOD(DaysSinceStart, CycleDays) >= RotationDaysOn,
    1,  // offday
    0   // workday
)

Colour Format = 
IF([Value_per_day] = "V", "#fed70d",
IF([Value_per_day] = "U", "#fed68d",
IF([IsShiftOffDayMeasure] = 1, "#FFB6C1"
)))

Result for your reference:

Best regards,

Joyce

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

[coochy5]

Joyce,

Many thanks for your assistance again !

Almost there - I think somehow you had an older file - I had added a shift days off table in the sample data I see - I don't think it's in referenced in the measure - I see rotation mentioned, but it's crew colout that needs to be the driver of the shift days off calculation.

For example - in your example you sent back - both Edgar and Sean are on the same rotation (15/6) and on the same crew (colour) - yellow - so the shift days off would be the same for all of the yellow crew (who only have a 15/6 rotation).

Any further thoughts ?  

The shift table - in my sample data is named 'Shift schedule'.  I could try and upload another copy, but that seems to not work correctly.  

This is it's format...if that helps ?

I really appreciate your assistance with this !

[Anonymous]

Hi @coochy5 , 

Thank you for the file. I will need some time to work on it. I appreciate your patience

[coochy5]

Hi,

Trying again.  Not quite sure why the sharing has been a challenge.

This is my sample pbix - all data has been changed - with a good selection of event dates (all event dates can be treated the same way - vacation/parental etc).  I've added a shift schedule table also.  From a vacation standpoint - this is working well, but I would like to try and overlay the shift schedule days off.

https://drive.google.com/file/d/1OoPk-sxRww6XIrs2zbSaMUwz8BtZ8Wlz/view?usp=sharing 

The result I am trying to achieve is this - where the yellow indicates the vacation, and the red is the shift schedule time off