Forum Discussion
Helper IIPBI - show shift days off in calendar - matrix visualisation
Hi coochy5 ,
It sounds like you’re working on a complex but interesting project in Power BI. To incorporate shift days off into your calendar visualization, you can follow these steps:
Create a Shift Pattern Table:
- Create a new table in Power BI to define your shift patterns. This table should include columns for the shift name, days on, and days off.
ShiftPattern = DATATABLE(
"ShiftName", STRING,
"DaysOn", INTEGER,
"DaysOff", INTEGER,
{
{"Shift1", 22, 20},
{"Shift2", 22, 13},
{"Shift3", 15, 6},
{"Shift4", 5, 2}
// Add other shifts as needed
}
)
Create Shift Calendar:
- Create a calculated table to generate the shift calendar based on the start date and the shift patterns.
ShiftCalendar =
VAR StartDate = DATE(2024, 1, 1) // Replace with your actual start date
VAR EndDate = DATE(2025, 12, 31) // Replace with your desired end date
VAR DateRange = CALENDAR(StartDate, EndDate)
RETURN
DateRange
Create Shift Name measure:
At last, create a measure to generate the shift status(on/off).
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
coochy5Hi,
Thank you so much for the info - greatly appreciated.
I would somehow need to incorporate these shift days off, into the same matrix - which is currently handling vacation/unpaid time off from a colour format measure - which hopefully you can see from the image.
The vacation/unpaid piece was relatively simple to figure out - as that's a booked day per individual from a vacation table.
The shift days off, are giving me a headache as they have crew colours, on the same rotations, but the schedule will be different - offset. Have no idea how to handle that.
I had cased the original data via a sql view, to split out the rotation schedule and crew colour - original, v derived values are :
Example from the 15/6 rotations.
Work Schedule Calendar Day Breaker Day Breaker Divides Work Week Start Day Biweekly Calculation Start Date Configurable Calculation Period Day Rate / Bulker/ Salary - 15/6 Rotation 1 (Blue) 12:00 a.m. Wednesday 2/22/2017 Month Starting on Day 1 Day Rate / Bulker/ Salary - 15/6 Rotation 3 (Yellow) 12:00 a.m. Wednesday 2/15/2017 Month Starting on Day 1 Any tips on how I could incorporate the 2 seperate measures into perhaps a single one, as the matrix only accomodates a single value choice ?
Measures:Colour Format =IF([Value_per_day] = "U", "#fed68d",IF([Value_per_day] = "V", "#fed70d"))ShiftName =VAR CurrentDate = MAX('ShiftCalendar'[Date])VAR DaysOn = SELECTEDVALUE(ShiftPattern[DaysOn])VAR DaysOff = SELECTEDVALUE(ShiftPattern[DaysOff])VAR CycleLength = DaysOn + DaysOffVAR CyclePosition = MOD(DATEDIFF(CALCULATE(MIN('ShiftCalendar'[Date]),ALLSELECTED(ShiftCalendar)), CurrentDate, DAY), CycleLength)RETURN IF(CyclePosition < DaysOn, "On", "Off")- coochy5
Hi
Many thanks for the response. I'm looking to incorporate the shifts days off, into an existing matrix, but the shift days off are offset, by crew colours and shift rotation.
This is what my report looks like at the moment.
Crew colours have different rotations and therefore different shift off days.
Crew Rotation Schedule Magenta 1 15/13 Pink 2 15/13 Blue 1 15/6 Red 2 15/6 Yellow 3 15/6 Purple 1 22/13 Grey 3 22/13 Teal 5 22/13 I'm really struggling to incorporate the days off into a single value I can show on the matrix. Is it possible ?
This is what I'm ultimately trying to replicate - compiled in C# - and is going away.
Very appreciative of any further assistance or suggestions !