BLOG

Calculate Working Days without Holidays for specific states in country

26-11-2018  0 Comment(s)

Recently I have a task to modeling for “Client Turnaround Report”. In this report, I need to calculate turnaround duration by counting all working days between appointment’s event date and finalization date. The problem is that working day is defined by:

  •  All week days  (Mon-Fri)
  • And week day that does not fall on a public holiday

Let’s have a look at my data structure in sample data:

  • One configuration table for public holiday of 2017 by date, state and holiday name of Australia.
  • One State table (NT, ACT, VIC, TAS, NT, NSW and QLD) and ALL is applied for all states.
  • One fact table for Appointment transaction with Id, StateId, ClientId (who books this appointment, EventDate and FinalizedDate)

 

So my expectation is calculating Turnaround for Appointment table: Turnaround =Number of week day from EventDate to FinalizedDate – Number of holidays in this period.

Firstly, I do some steps to transform the public holiday table with 2 steps:

  • Duplicate HolidayDate field
  • Pivot this table based on HolidayDateKey

I create 3 relationships for data model:

  • Appointment (StateId) – State table (StateId)
  • Appointment (FinalisedDate) – Dates table (DateKey). Cause this report will be analyzed by Finalisation Date)
  • Holiday (HolidayDate) – Dates table (DateKey)

So my target is creating a measure that should count all week day between EventDate and Finalisation Date and excluding its Holiday.

My solution:

  • Create 8 calculated column in Dates table as total working day for each state per date.

 

1

ACT_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[ACT])))

 

1

NSW_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[NSW])))

 

1

NT_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[NT])))

 

1

QLD_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[QLD])))

 

1

SA_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[SA])))

 

1

TAS_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[TAS])))

 

1

VIC_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[VIC])))

 

1

WA_WorkingDay = if(Dates[DayName]="Saturday" || Dates[DayName]="Sunday",0, 1 - if(related(Holiday[ALL])=1,1,related(Holiday[WA])))

 

  • Create TurnaroundTime as calculated column in Appointment table as total working day between event date & finalized date.

 

1

2

3

4

5

6

7

8

9

10

11

TurnaroundTime = if(ISBLANK(Appointments[FinalizedDate]) || ISBLANK(Appointments[EventDate]), Blank(),

if(RELATED(States[State])="ACT",calculate(sum(Dates[ACT_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="NSW",calculate(sum(Dates[NSW_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="NT",calculate(sum(Dates[NT_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="QLD",calculate(sum(Dates[QLD_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="WA",calculate(sum(Dates[WA_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="SA",calculate(sum(Dates[SA_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="TAS",calculate(sum(Dates[TAS_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="VIC",calculate(sum(Dates[VIC_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate])),

if(RELATED(States[State])="",calculate(sum(Dates[QLD_WorkingDay]),DATESBETWEEN(Dates[DateKey],Appointments[EventDate],Appointments[FinalizedDate]))

)))))))))-1)

 

  • Create Turnaround measure as average of TurnaroundTime column.

 

1

Average of Turnaround = average(Appointments[TurnaroundTime])

g! I could analyze turnaround by state and client through 12 months now.  For more details, you could check it in these sample reports: Turnaround

Comment Here

Comments

No Comments to Show

WE ALWAYS WORK WITH :