Working days

[potap]

Hi!

Here is my problem :

Our year-end date is March 31 and the last pay of the year always overlaps the first pay of the following year. I need to have the number of working days between the StartDate and March 31 of the year. I also need to ignore Holidays. I have a Holiday table in Power Query that comes from my Calendar table.

Year	PayYear_ID	Pay_StartDate	Pay_EndDate	WorkingDays_YearBefore
2014	1	3/20/14	4/2/14
2015	1	3/19/15	4/1/15

Thank you!

 

[Ken Puls]

So this was a bit tricky, but I knocked up something that I think will do the trick. I assume you're paying bi-weekly, so knocked up a table of Pay_Start/Pay_End dates through 2031 for testing. Given that:

• I loaded this table as a Connection Only query called "PayCutoffs"
• I created a "Holiday" table of the Good Friday stats that might impact it and loaded this as Connection Only

The next thing I did was build a dynamic calendar for just vacation days that spanned from the very first day in the Pay_StartDate column to the last date in the Pay_EndDate column. (I had to do this since I didn't have a calendar table, you may be able to retrofit this to use your calendar.) The trick was that I needed to do the following:

• Add a column for Weekdays
• Merge in the Holiday table
• Filter the Weekdays column to exclude values of 0 (Sunday) and 6 (Saturday)
• Filter the Holiday column to only include null values (removing all holidays)

I called that query "WorkDayCalendar" and loaded it as a connection only as well, just to show the logic.

At this point I did the following to create the "fxWorkDaysPriorYear" function:

• Created a reference to the WorkDay Calendar
• Filtered the Dates column with a custom filter:
  • After or equal to 3/20/2014 and Before or equal to 3/31/2014
• Grouped the rows
  • I removed the grouping columns
  • I changed the column name to "WorkDays_PriorYear"

At this point I was left with a value of 8 days for 2014.

As this looked good, I then converted the new query into a function by editing the M code, making the following changes:

(dtStart as date)=>

let
    Source = WorkDayCalendar,
//    #"Filtered Rows" = Table.SelectRows(Source, each [Dates] >= #date(2014, 3, 20) and [Dates] <= #date(2014, 3, 31)),
    #"Filtered Rows" = Table.SelectRows(Source, each [Dates] >= dtStart and [Dates] <= #date(Date.Year(dtStart), 3, 31)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"WorkDays_PriorYear", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

Summary of the changes are that I added the line at the top to turn it into a function, copied the Filtered Rows line and subbed in the variable to dynamically drive the query. I then changed the name to "fxWorkDaysPriorYear".

Finally, I used the function to generate the results against the original query:

• Created a new query referencing the PayCutoffs query
• Added a custom column with the following formula: =fxWorkDaysPriorYear([Pay_StartDate])
• Expanded that column

I've attached a sample workbook with the work done. The final results are in the WorkingDays query.

Also, not sure if you have my book, but Chapter 21 discusses creating custom functions (although you'll get a deeper understanding by reading Chapters 20-22.)

 

[potap]

Wow thanks! It works perfectly.

I do have your book but I still need to find time to read it :S

 

[Bill Szysz]

Other approach....

Regards

 

[Ken Puls]

Nice work, Bill. (As usual!)

 

[Bill Szysz]

Thanks for your kind words, Ken.
I just wanted to show that in this case (or similar) we do not need a calendar table.

Regards

 

[potap]

Thanks!

 

[Ken Puls]

I just wanted to show that in this case (or similar) we do not need a calendar table.

True enough. Although, to be honest, if I'm loading the data to Power Pivot, I ALWAYS have a calendar table anyway. But I do get your point.

 

[potap]

Ken, I find your version easier to understand for the basic user.

 

[potap]

Hi again!

Bill, I am trying to use the file you shared here to calculate the working days/hours/minutes between two dates.

Here are my problems :

The transformation of the hours in a days/hours/minutes format (need to work if I have 400 days)
Exclude the lunch break
Include the Worday table because some employees work 3 or 4 days a week

EDIT : would also be really appreciated if you could help me with a formula that add X days X hours X minutes to the Start date so I could get the End date automatically in some particular situations where I have the number of working days and the start date.

Thank you very much!