Forum Discussion
coochy5
Helper III
Helper IIINeed to create a shift (days off) calendar in matrix visualisation - overlaid with vacations
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 ???
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...
