THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Working days computation in PowerPivot

A very frequent question, when performing business analysis, is to count the number of working days in a certain period of time. Creating a data model with PowerPivot that gracefully handles working days is pretty easy, nevertheless it is worth a post.

As you might already know, PowerPivot needs a calendar table to compute most of its time intelligence functions and this table needs to satisfy some important conditions:

  • There should be no hole in the whole calendar, i.e. all dates of a year should be present.
  • The key of the table should be of DATETIME type.

Building this table is really straightforward using Excel: start filling a column with dates (use alt-dragging to fill it very quickly) and then add some very simple formulas to the worksheet, to obtain a table like the following one. Clearly, the number of dates that should be included in the table really depends on your data, always remember that the table should always start at the 1st of January and end at the 31 of December, with all the necessary years included. Leaving a hole means getting wrong data later.

image

Before loading the table inside PowerPivot, it is necessary to add some info about working days and vacation, due to the title of this post Sorriso. To do this, let us create a new Excel table containing the holidays, with a description of the non working reason, like the following one:

image

You can add as many vacation days as you need to this table (and, if you wonder whether I consider holding a PowerPivot Workshop vacation… well, I surely enjoy it: it is very close to vacation!). Then, using Excel VLOOKUP function, we can bring the information about vacation directly inside the Calendar table, which now will contain some new columns:

image

The interesting formulas are:

  • WorkingDays =IF (OR ([@WeekDay] = 6, [@WeekDay] = 7), 0, IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), 1, 0))
  • Holiday =IF([@WorkingDays]=0,"Holiday","Working Day")
  • HolidayReason =IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), [@Holiday], VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE))

Here we state that a day is a working day if it is not Saturday or Sunday and it does not appear in the holidays table. Other formulas are very simple ones and define a string representation of holiday and holiday reason, as you can see from the figure. Please note that the WorkingDays column is an integer, holding 0 or 1. Moreover, I decided to name it plural instead of singular… this is not a mistake, it will make sense in the next figure.

If you now create a PivotTable using only the new Calendar table and put Year on columns, MonthName on rows and the WorkingDays column as value, you get this first nice result, which shows the number of working days for each month. Due to the fact that the value is computed by simply summing values, it works even with different period selections, with or without holes.

image

I have already put inside PowerPivot some tables coming from the AdventureWorks database, in particular iI have the SalesOrderHeader table, which contains the OrderDate. Thus, I can create a relationship based on the order date, as you can see in the following figure:

image

Having done this, you can use the WorkingDays column to compute, for example, the average amount sold per working day, using this formula:

AmountPerWorkingDay=SUM (SalesOrderDetail[LineTotal]) / SUM (Calendar[WorkingDays])

Which provides an interesting report:

image

Well… this report looks interesting, yet it is not working as we would expect, since the average of 2001 is not 44,092,64. The issue here is that the sum of working days in 2001 is 257 but not all the months contain values for sales, yet their working days are counted in the denominator. If we really strive for perfection (which we do) then it is necessary to leverage some basic DAX knowledge and change the formula of the AmountPerWorkingDay to filter out the months where there are no sales. A more correct formula is the following:

=  SUM (SalesOrderDetail[LineTotal]) 
    / 
    SUMX (VALUES (Calendar[MonthNumber]),
        IF (
            CALCULATE (SUM (SalesOrderDetail[LineTotal])) = 0,
            0,
            CALCULATE (SUM (Calendar[WorkingDays]))
        )
    )

The complete explanation is a bit long, here we basically iterate over months and, if a month does not contain values for the sales, we remove it from the count of working days. Take some time to study it, then move on. Sorriso The result is:

image

Which now shows the correct result and a good course in the value, which grows year over year. AdventureWorks business is going in the right direction!

Nevertheless, as it has been correctly noted by @Reuvain in the comments, the Grand Total column is still incorrect, since we have solved the issue with months but not with years. The value of zero over January, 2001 is counted to perform the average of January Grand Total. To solve this issue we need to iterate over years too, as in this (please note the outer and inner loop, over years and months).

=  SUM (SalesOrderDetail[LineTotal]) 
    / 
    SUMX (VALUES (Calendar[Year]),
        SUMX (VALUES (Calendar[MonthNumber]),
            IF (
                CALCULATE (SUM (SalesOrderDetail[LineTotal])) = 0,
                0,
                CALCULATE (SUM (Calendar[WorkingDays]))
            )
        )
    )

Using this new formula the values will be correct at the grand total column too:

image

Now that we can decide whether a day is working or not, there is another very interesting computation that I would like to show: computing the difference, in working days, between two dates. Computing the delta between two dates is a matter of a subtraction in PowerPivot, since dates are stored internally as floating numbers. But, how many working days do exist between two dates is a different problem, which requires some DAX acrobatics.

Before moving on, we understand that we need at least two dates to perform a delta. Luckily, in the SalesOrderHeader table there are the OrderDate and the ShipDate, it might be interesting to calculate the difference, in working days, between these two dates. Now, time to think at the algorithm.

The main idea is to assign an incremental number, to each date, that increases by 1 only on working days and remains the same over holidays. It is much easier to understand it looking at the next figure, the value of WorkingDayNumber in the box.

image

You can see that the WorkingDayNumber column contains the value 3 from January 4 to January 6, since the last two days are vacation. If we had such a value, then to compute the delta in working days between two dates, it would be enough to perform a simple subtraction between the two WorkingDayNumbers. Now, the issue is how to compute such a value.

It is not easy if you are not familiar with the way PowerPivot formulas need to be expressed but, believe me, after some time with PowerPivot the formula will come to your mind in a snap. The idea is to define a calculated column that counts the number of dates before the current one filtering only working days. Or, said in other words, sum 1 for working days, 0 for holidays, for all the dates that exist before the current one. Guess what? We have a column that contains 0 for vacation and 1 for working days: it is the WorkingDays column, so we need to sum it up for all the dates before the current one.

Here is the formula:

WorkingDayNumber = CALCULATE (SUM(Calendar[WorkingDays]), Calendar[Date] <= EARLIER(Calendar[Date]))

Take your time to study and understand it… it is something that requires you to have already touched the Karma of PowerPivot… Once you have digested it, it is time to bring this information inside the SalesOrderHeader table. In fact, the SalesOrderHeader table contains two dates (ShipDate and OrderDate) but we need to compute the difference between the related WorkingDayNumber, which are not part of the SalesOrderHeader table.

Since there exists a relationship between SalesOrderHeader and the calendar table, the value of the WorkingDayNumber can be easily computed using RELATED. The value for ShipDate, on the other hand, needs a slightly more complex DAX formula, since we need to search, inside the Calendar table, the value of WorkingDayNumber for the date that equals ShipDate. Here are the formulas:

  • WorkingDayOrder = RELATED (Calendar[WorkingDayNumber])
  • WorkingDayShip = CALCULATE (VALUES (Calendar[WorkingDayNumber]), Calendar[Date] = EARLIER (SalesOrderHeader[ShipDate]))
And now, the final result is simply computed by subtracting the WorkingDayShip from the WorkingDayOrder, which returns the number of working days that passed from the date of the order to the date of the shipment. Sounds easy? No, it is not very easy… yet it can be done and, after having studied it, you will find it easy. PowerPivot requires you to change your mind and think in the DAX way, roll up your sleeves and start studying! Sorriso

This is just another example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months.

You can find more info on www.powerpivotworkshop.com. Hope to see you  there!

Published Wednesday, January 19, 2011 9:00 AM by AlbertoFerrari

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Fritz said:

Nice post, very informative but isn't it easier to use the "workday" formula instead of =IF (OR ([@WeekDay] = 6, [@WeekDay] = 7), 0, IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), 1, 0)).

I don't have my laptop at the moment so I can't test that but I think it can be done in less calculations, something like =WORKDAY([@Date],0,HolidaysTable[#Data])

January 19, 2011 9:14 AM
 

AlbertoFerrari said:

@Fritz,

The nice part of writing about PowerPivot is that I understand how ignorant I am with Excel. Users, like you, who know Excel better than me suggest me how I should write Excel formulas, it is happening for each PowerPivot post of mine. :)

I love this. Really.

I have not tested your formula but, even before checking it, I am pretty sure that it will work better than mine. After all, I am a BI guy, not an Excel one.

Thus, take my good part (DAX) and improve my bad one (Excel), which is exactly what you have done. :) Thanks alot.

January 19, 2011 9:25 AM
 

Reuvain said:

@Alberto:

Wouldn't the Grand Total column still be incorrect even with the DAX formula? My understanding is that the denominator would include the number of workdays in months that have zero sales (e.g. January-June 2001 and August-December 2004) since those months do have sales for all years combined. It is only in the column context of a single year that the DAX formula would exclude the months with zero sales. I think something a bit more complicated would be required for the Grand Total column to be correct.

January 19, 2011 12:58 PM
 

AlbertoFerrari said:

@Reuvain,

The formula is correct for the grand total at the year level, where the filter context already contains a selected year.

You are probably right (I need to check it, but it seems that your comment is right) for the grand total at the row level and, obviously, for the grand total itself.

By enclosing the formula inside another iterator over years you should get the right result. I will post the correct solution for the grand total for the rows in a few hours (it's dinner time here in Italy; wife and baby are looking for some of my time :))

Thanks a lot for pointing out this!

January 19, 2011 1:27 PM
 

AlbertoFerrari said:

@Reuvain,

The formula is now correct. :)

Thanks again for your comments, it is a real satisfaction to know that my readers are so careful!

January 19, 2011 4:25 PM
 

Bob Phillips said:

Hi Alberto,

I think yu have made a big mistake in your Workday formula because you are using the Weekday function incorrectly.

Weekday returns 2 for Mon, 3 for Tue, ..., 1 for Sun, if the return_type is 1 or omitted, not 1, 2, 3, ..., 7. So your test

OR ([@WeekDay] = 6, [@WeekDay] = 7)

is not finding Sat and Sun, but Fri and Sat. If you look at your table, it shows 5th Jan 2001 as a holidaty, whereas that date is a Friday (it would be nice if Friday's were a holiday!).

It can be easily corrected of course

OR ([@WeekDay] = 1, [@WeekDay] = 7)

or better still, if you use the return_type of 2 to determine Weekday

=WEEKDAY(@Date, 2)

then you can use

IF('Date'[WeekDay]>5, 0, IF(ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE), 1, 0))

You could also combine them in one IF statement

=IF(OR([@WeekDay]>5,NOT(ISNA(VLOOKUP([@Date],HolidaysTable[#Data],2,FALSE)))),0,1)

oreven do away with the IF completely

=--(NOT(OR([@WeekDay]>5,NOT(ISNA(VLOOKUP([@Date],HolidaysTable[#Data],2,FALSE))))))

(BTW, the double unary -- does not work in PowerPivot, but you can avoid IFs with something like

=(Weekday('Date'[Date],2)>5)*1)

BTW, when I create date tables like this I build a list a long way into the future, but I don't want to have all future years showing in my pivots. So I add a worksheet activate event to resize my dates table to the end of the current year only. This is the code I put in my Dates worksheet activate event

Private Sub Worksheet_Activate()

Dim NumCols As Long

Dim RowNum As Long

   NumCols = Me.Range("A1").End(xlToRight).Column

   RowNum = Application.Match(CLng(DateSerial(Year(Date) + 1, 1, 0)), Me.Columns(1), 0)

   Me.ListObjects("Date").Resize Range("$A$1").Resize(RowNum, NumCols)

   Me.Range("A2").Resize(1, NumCols).AutoFill Me.Range("A2").Resize(RowNum - 1, NumCols)

End Sub

Thus, in my pivot slicer I only have years up to the current year, not 2012, 2013 etc. Helps to reduce the cluttter.

January 24, 2011 4:53 AM
 

AlbertoFerrari said:

@Bob, thanks a lot for your comment.

As you might imagine, I have not used this workbook for a customer's project, otherwise the error would have been evident. Creating samples always leads to this kind of imperfection. :)

Your code, moreover, seemes interesting too, I am still learning how powerful Excel might be. Believe me, I play all day long with complex BI patterns but still find difficult to code VBA for Excel.

January 24, 2011 5:03 AM
 

Alberto Ferrari said:

I spend most of my time developing complex BI solutions and, doing that, I always talk with DBA, developers,

January 24, 2011 5:51 AM
 

Bob Phillips said:

Hi Alberto,

Yes, Excel is truly powerful. Combining Excel's builtin functionality and VBA, so truly staggering results can be obtained.

I appreciate your background, but now you are in the self-service BI arena, you are going to need to develop those Excel skills :)

January 24, 2011 5:59 AM
 

Claire said:

Hi Alberto,

Do you have to name each individual vacation/holiday day? Could you instead specify a range of dates (for example if you were going on a two-week long holiday?)

January 31, 2011 1:04 PM
 

AlbertoFerrari said:

@Claire,

Of course you can. If you do that in the Excel table (which I suggest), it is just a matter of adjusting the VLOOKUP so that it works on ranges instead of with a direct match.

On the other hand, you can achieve the same goal with DAX (in the book we use this latter template, if I remember well) and, in that case, with a formula like CALCULATE (VALUES (...)) you will get the same result as the VLOOKUP.

Nevertheless, keep in mind that handling vacation in DAX means loading the two tables (dates and vacation) in PowerPivot and this might be an issue if you need more than one calendar table (for example, for date and ship date). You will end up duplicating each table for each role, which might end up being cumbersome.

I personally suggest to use Excel to create the full calendar table with vacations and then load the final table in PowerPivot, life will be much easier.

January 31, 2011 1:27 PM
 

ade said:

Heyyyyyy, I have a start and end date! in a pivot table or chart can i work out the range total between the two dates?

Ade

August 8, 2011 10:17 AM
 

AlbertoFerrari said:

@Ade, I need an example of what you want... drop me an email, and I'll try to help you on that.

A sample workbook would be fine.

August 8, 2011 4:29 PM
 

greenemg said:

Alberto,

I have a different request.  We have a social services data set and want to take that data (clientid, visitdate, visitoutcome) and see if the same client had a visit in the 6 months prior to any visit.

What is the best/easiest way to do that at the PowerPivot level?

November 7, 2011 10:52 AM
 

AlbertoFerrari said:

@GreenEng (and Everybody else)

Please provide me details of any request via e-mail, I cannot provide support through the comments.

I don't promise to give a solution to all of the incoming mails but comments here are definitely the wrong place where to submit specific issues.

November 7, 2011 11:05 AM
 

Bob Mick said:

Alberto,

Nice post.  FYI: I am more interested in DAX techniques, that perfect examples.  You gave me a couple of good ones in this post.

Thanks,

Bob

December 2, 2011 9:31 AM
 

Normbi said:

Alberto, Needed post.

The DAX:

WorkingDayNumber = CALCULATE (SUM(Calendar[WorkingDays]), Calendar[Date] <= EARLIER(Calendar[Date]))

didn't work on my end. Would like someone to look at it. You are right, proficiency in DAX is l'ultima destinazione.

November 3, 2012 6:02 PM
 

Eltjo Verweij said:

@Fritz

The actual formula in Excel is

=NETWORKDAYS([@Date],[@Date],HolidaysTable[#Data])

This will show 0 for weekends and holidays, 1 for workdays.

March 26, 2013 6:29 AM
 

Renato Lyke said:

Hi,

I have 3 tables where i have the month end date. Table A has the name of the employee, the date the tasks have been completed for each month. Table B has the name of the employee and the emp id. Table C has the employee id and joining date. There is a relationship between these tables. I have created a calculated column in table b to give me the month end date of the person when they joined. I am trying to create a measure where i could calculate the experience of the person based on monthend date from table A, which would help calulate the target for no of task they need to complete each month. It is not working any suggestions who it could be completed?

May 31, 2013 7:15 AM
 

Renato Lyke said:

=(if(countrows(values(MA_Dates[Month_End_Date]))=1,

    if(values(MA_Dates[Month_End_Date])<=LASTDATE('Employee Details'[Joining_Month_End])

&&values(MA_Dates[Month_End_Date])>=dateadd(lastdate('Employee Details'[Joining_Month_End]),(max(Period[Period])*-1),month),calculate(DATESBETWEEN('Employee Details'[Joining_Month_End],dateadd(lastdate(MA_Dates[Next_Month_Start_Date]),-1,MONTH),lastdate(values(MA_Dates[Month_End_Date]))))*-1)))*-1

I tried this code by creating an additional table called MA_Dates along with the earlier tables. Still it does not work.

May 31, 2013 8:21 AM
 

How to retun a value from XX working days back in a calculated field said:

June 23, 2014 11:34 PM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement