Forum Discussion

coochy5's avatar
coochy5
Icon for Helper II rankHelper II
2 years ago

PBI - show shift days off in calendar - matrix visualisation

Hi all,

Relative noob trying to figure this out in PBI.

Trying to create a shift calendar with 8 different shift patterns - all complete days, no specific times to handle thankfully.

I'd need to define a start date for each - and they'd run without a stipulated end date.

Shifts are : (days on/days off)

22/20

22/13

15/6

5/2 

etc, etc.

 

I've managed to create the visualisation with a matrix to show vacation days, and I'd need to incorporate the shift days off, if possible, and there should be no overlap with vacation/shift days off as vacation is a separately recorded event (vacation table).

 

Any help is gratefully received !

Need Help
Tips and Tricks

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    When is each shift cycle starting?

     

    Please provide all cycles, and the desired time range. I assume you don't need that for past dates?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

    • coochy5's avatar
      coochy5
      Icon for Helper II rankHelper II
      2 years ago

      Hi,

      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 CalendarDay BreakerDay Breaker DividesWork Week Start DayBiweekly Calculation Start DateConfigurable Calculation Period
      Day Rate / Bulker/ Salary - 15/6 Rotation 1 (Blue)12:00 a.m. Wednesday2/22/2017Month Starting on Day 1
      Day Rate / Bulker/ Salary - 15/6 Rotation 3 (Yellow)12:00 a.m. Wednesday2/15/2017Month 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 + DaysOff
          VAR CyclePosition = MOD(DATEDIFF(CALCULATE(MIN('ShiftCalendar'[Date]),ALLSELECTED(ShiftCalendar)), CurrentDate, DAY), CycleLength)
          RETURN IF(CyclePosition < DaysOn, "On", "Off")

       

       

       

       

    • coochy5's avatar
      coochy5
      Icon for Helper II rankHelper II
      1 year ago

      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.

       

      CrewRotationSchedule
      Magenta115/13
      Pink215/13
      Blue115/6
      Red215/6
      Yellow315/6
      Purple122/13
      Grey322/13
      Teal522/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 !