Forum Discussion
Helper IINeed 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...
| 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) |
Still unable to upload pbix....
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...
11 Replies
jgeddesSuper User
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.
coochy5Hi,
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 ?
- 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