topic Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation in DAX Commands and Tips

Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

coochy5 — Tue, 26 Nov 2024 02:09:02 GMT

Hi all,

Looking for some assistance on a task that is making me question my sanity (of perhaps starting the project).

Replacing an older C# app.

Need to a calender (matrix view) with booked days off combined with rotation days off.

The data source for the vacations is a WorkDay table - sample data attached.

6 distinct rotation schedules & 8 distinct crews (colours).

The crew colours are the differentiator for the days off - 15/6 red would not be off at the same time as 15/6 yellow.

This, cannot be a correct result for example - all red crew would have the same shift days off.

I'm having a challenge getting the rotation schedules and crews (colours) mapping on the matrix correctly - and that's prior to adding the vacation days off - which are also a nightmare.

I do have a version of this report out in production - but that's only with the vacation days - that's working a charm, but I cannot get the shift days off calculated correctly.

This is what I am trying to replicate:

Any help is graciously received !

(adding a link to my sample pbix)

https://drive.google.com/file/d/1hADMpk_o6MVbxUDvjD5cRBv0YZinQY8D/view?usp=drive_link

Here's my worker data, in a table...

Name

LastName

FirstName

WD_ID

Active

Title

DivisionId

BaseId

Date

TimeType

WorkScheduleCalendar

Rotation

Crew

Owen Money

Money

Owen

216

Supervisor 3

SPO

1/19/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

Supervisor 2

MOO

11/28/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

Supervisor 2

MOO

11/29/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

Supervisor 2

MOO

11/30/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

Supervisor 2

MOO

1/21/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Fred Bloggs

Bloggs

Fred

5860

Supervisor 2

MOO

4/18/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

12/29/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

11/25/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

11/26/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

12/30/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

11/27/2004

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Dog Barker

Barker

Dog

2101

Operator 4

HIT

12/31/2024

Unpaid Time Off

Day Rate / Bulker - 15/6 Rotation 2 (Red)

15/6

Red

Will Rodgers

Rodgers

Will

9961

Operator Frac Blender 4

IDK

12/29/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Will Rodgers

Rodgers

Will

9961

Operator Frac Blender 4

IDK

12/10/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Will Rodgers

Rodgers

Will

9961

Operator Frac Blender 4

IDK

12/11/2024

Unpaid Time Off

Day Rate / Bulker - 15/13 Rotation 1 (Blue)

15/13

Blue

Kathy Finger

Finger

Kathy

2223

Assistant Executive

CAT

12/24/2024

Vacation (Days)

Day Rate / Bulker - 15/6 Rotation 4 (Purple)

15/6

Purple

Kathy Finger

Finger

Kathy

2223

Assistant Executive

CAT

12/30/2024

Vacation (Days)

Day Rate / Bulker - 15/6 Rotation 4 (Purple)

15/6

Purple

Sean Potts

Potts

Sean

10341

Tech II

POD

12/17/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

Tech II

POD

12/18/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

Tech II

POD

1/2/2025

Vacation (Days)

22/13 (Black)

22/13

Black

Sean Potts

Potts

Sean

10341

Tech II

POD

12/16/2024

Vacation (Days)

22/13 (Black)

22/13

Black

Brianna Williams

Williams

Brianna

Supervisor 1

SPO

11/29/2024

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 3 (Yellow)

15/6

Yellow

Nicky Fasterhaus

Fasterhaus

Nicky

1265

Operator 1

CTC

1/1/2025

Unpaid Time Off (Days)

Day Rate / Bulker - 15/6 Rotation 3 (Yellow)

15/6

Yellow

I have this in a calendar table:

Value_per_day =

LEFT (

MAXX (

FILTER (

ALLSELECTED ( 'Worker_Vacay' ),

'Worker_Vacay'[Date] = MAX ( 'Calendar'[Date] )

&& 'Worker_Vacay'[Name] = MAX ( 'Worker_Vacay'[Name] )

'Worker_Vacay'[TimeType]

)

and I have this, to calculate whether the day is a shift on or off day.

IsShiftOffDayMeasure =

VAR RotationDaysOn = VALUE(LEFT(SELECTEDVALUE('Worker_Vacay'[Rotation]), FIND("/", SELECTEDVALUE('Worker_Vacay'[Rotation])) - 1))

VAR RotationDaysOff = VALUE(RIGHT(SELECTEDVALUE('Worker_Vacay'[Rotation]), LEN(SELECTEDVALUE('Worker_Vacay'[Rotation])) - FIND("/", SELECTEDVALUE('Worker_Vacay'[Rotation]))))

VAR StartDate =

CALCULATE(

MIN('Worker_Vacay'[Date]),

ALLEXCEPT('Worker_Vacay', 'Worker_Vacay'[Crew], 'Worker_Vacay'[Name])

)

VAR DaysSinceStart = DATEDIFF(StartDate, SELECTEDVALUE('Calendar'[Date]), DAY)

VAR CycleDays = RotationDaysOn + RotationDaysOff

RETURN

IF(

MOD(DaysSinceStart, CycleDays) >= RotationDaysOn,

1, // offday

0 // workday

)

Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

Anonymous — Tue, 26 Nov 2024 08:04:36 GMT

Hi, coochy5

Firstly, regarding the PBIX file you shared, I am currently unable to access it due to privacy protocol issues. However, I have reviewed your previous questions and obtained the PBIX file related to the relevant responses.

Therefore, if your data requirements have been updated, please refer to the following link:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

When uploading a file, please be careful to delete sensitive information.

Based on my understanding of your question, you require the same rest period for the same Crew. Please replace the DAX with the following content:

IsShiftOffDayMeasure = VAR Rotation = SELECTEDVALUE ( 'FACT'[Rotation] ) VAR RotationDaysOn = VALUE ( LEFT ( Rotation, FIND ( "/", Rotation ) - 1 ) ) VAR RotationDaysOff = VALUE ( RIGHT ( Rotation, LEN ( Rotation ) - FIND ( "/", Rotation ) ) ) VAR StartDate = CALCULATE ( MIN ( 'FACT'[Date] ), ALLEXCEPT ( 'FACT', 'FACT'[Crew] ) ) VAR DaysSinceStart = DATEDIFF ( StartDate, SELECTEDVALUE ( 'Calendar'[Date] ), DAY ) VAR CycleDays = RotationDaysOn + RotationDaysOff RETURN IF ( MOD ( DaysSinceStart, CycleDays ) >= RotationDaysOn, 1, 0 )

Here are the final results:

If I have misunderstood your requirements, I would appreciate your prompt correction, along with a diagram to illustrate your needs.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

coochy5 — Wed, 27 Nov 2024 15:32:59 GMT

Hi. This is great. It conceptually works ! Can I ask for some further advice ? The days on/off schedule varies by for the crew colour and rotations - and the calendars have different commencement dates. I'm not sure, in the measure you supplied, how the start date of each rest period is calculated. How should I incorporate the shift patterns - a seperate table referenced in the measure ?

Example data:

Id	Name	DaysOn	DaysOff	Color	EffectiveDate	IsActive	WorkdayName
1	Red	15	6	#ff0000	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)
2	Yellow	15	6	#dede00	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)
3	Blue	15	6	#0000ff	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)
4	Teal	22	13	#008B8B	5/30/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 5 (Teal)
5	Orange	22	13	#ff7f27	5/9/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 2 (Orange)
6	Purple	22	13	#a349a4	5/2/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 1 (Purple)
7	Brown	22	13	#b97a57	5/23/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 4 (Brown)
8	Grey	22	13	#708090	5/16/2018	1	Day Rate / Bulker / Hourly - 22/13 Rotation 3 (Grey)
9	Magenta	15	13	#f966f9	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)
10	Pink	15	13	#fe7c89	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)

Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

Anonymous — Thu, 28 Nov 2024 09:19:27 GMT

Hi, coochy5

Thank you for your prompt response.

I have imported your new table as the 'color' table:

Next, you can replace the value section of the matrix visual with the following measures:

Colour Format1 = IF ( [Value_per_day] = "V", "#fed70d", IF ( [Value_per_day] = "U", "#fed68d", IF ( [IsShiftOffDayMeasure] = 1, VAR cc = MAX ( 'FACT'[Crew] ) RETURN CALCULATE ( MAX ( 'color'[Color] ), FILTER ( 'color', 'color'[Name] = cc ) ) ) ) )

Then, adjust its colour settings:

Here are the final results:

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

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

Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

coochy5 — Thu, 28 Nov 2024 17:37:01 GMT

Hi.

Thanks again for your kind assistance. I think I'm unintentionally misleading here, or not being clear on the requirement.

Crew members are allocated to a shift schedule - eg 15/6, 22/13 - 15 days on, 6 off, 22 days on, 13 off etc.

The crew colour denotes the rotation - so that the rotations are offset.

Lots of crew will be on a 15/6 shift schedule, but will be on different rotations, so they are not off at the same time.

I've tried to incorporate your suggestions into my dev report - and it shows all crew colours off at the same time.

Not sure how I am getting this so wrong!

The measures - I believe, are identical - just changed table names.

I've added the same 'Color' table that you have used in your version back to me.

Curiously - I show no shift days off prior to 12-Dec-24, which is also not correct - in my live dataset. Some crews are already off.

Lastly - on the values in the matrix grid - I only need to display 2 colours - the shift days off would all be the same colour, and vacation/other time off will be the second colour.

Here's my result:

I wonder if it is worthwhile to create a new shift schedule table. Here's what I think it would look like - with the next set of effective dates for Dec 2024. (removed some schedules for ease)

Id	Name	DaysOn	DaysOff	EffectiveDate	IsActive	WorkdayName	NewStartDate(ShiftOn)	NextOffDate
1	Red	15	6	10/23/2024	1	Day Rate / Bulker - 15/6 Rotation 2 (Red)	4-Dec-24	19-Dec-24
2	Yellow	15	6	2/15/2017	1	Day Rate / Bulker - 15/6 Rotation 3 (Yellow)	11-Dec-24	26-Dec-24
3	Blue	15	6	2/22/2017	1	Day Rate / Bulker - 15/6 Rotation 1 (Blue)	27-Nov-24	12-Dec-24
4	Magenta	15	13	4/1/2020	1	Salary - 15/13 Rotation 1 (Magenta)	4-Dec-24	19-Dec-24
5	Pink	15	13	4/15/2020	1	Salary - 15/13 Rotation 2 (Pink)	18-Dec-24	2-Jan-25

Re: Looking for DAX assistance to combine vacation/shift days off in same calendar (matrix visualisation

coochy5 — Mon, 02 Dec 2024 19:29:39 GMT

Hi,

Were you able to offer any further insights into this ?

I managed to get the 2 time off periods colours sorted out (vacation/other & shift days off), but am unable to get the shift days off correctly represented in the offset form.

All shift days off seem to follow the same pattern, which is not correct, and not the result you achieved.