Calculate the number of business days between two dates in power pivot

2
votes

I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.

I do the same in T-SQL using the following query

DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
            CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
            CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END

I am looking for a similar query in PowerPivot.

3
powerpivotpowerbidax
As per standard Excel, Weekdays can be identified using the "weekday()" function. As for public holidays, well you'll need some sort of list of those for your area. You could then combine them in an "if" statement which is true if Weekday() returns 2-6 and the day is not a public holiday. This can then be used in a FILTER expression to reduce a table of dates to only those which are True. Let me know if this makes sense and I'll try to write it up as a full answer. – Soulus101
I should add, my Powerpivot is the old version (cursed corporate IT) so there may be some Time intelligence functions available to you that aren't to me. But the principle above should still apply. – Soulus101

3 Answers

2
votes

First you will need to create a table of dates. This will help you in this issue, and will enable you to use many other Time Intelligence Functions. You can create this manually or by using

CalendarDate | Month Key | Month Name | Quarter Name | Year
-----------------------------------------------------------
1/1/2014     | 1.00      | Jan        | Q1           | 2014
1/2/2014     | 1.00      | Jan        | Q1           | 2014
...
2/1/2014     | 2.00      | Feb        | Q1           | 2014
....
8/1/2014     | 8.00      | Aug        | Q3           | 2014
..
9/2/2014     | 9.00      | Sep        | Q3           | 2014
..
12/16/2014   | 12.00     | Dec        | Q4           | 2014

In your Dates Table can create a calculated column that determines if each day is a weekday. Call the column IsWeekday

=SWITCH(WEEKDAY([CalendarDate]),7,FALSE(),1,FALSE(),TRUE())

The SWITCH statement will return false if the day of week is the 7th or 1st day of the week (Saturday/Sunday)

Now in the table which contains your transactions you can create a new calculated column called Number Of Weekdays. This will 

=CALCULATE(COUNTA(Dates[IsWeekday]),
           DATESBETWEEN(Dates[CalendarDate], 
                        TransactionTable[date1],
                        TransactionTable[date2]
                       )
          )

Here are two articles that I recommend related to this approach.

0
votes

Another solution would be creating a quick measure column with the dax code below:

WeekDays =
SUMX(
    SELECTCOLUMNS(
        CALENDAR(min('MyTable'[Time1]), min('MyTable'[Time2])),
        "Date", min('MyTable'[Time1]),
        "BDay", IF(WEEKDAY([Date],3) < 5, 1, 0)
    ),
    [BDay]
)
0
votes

I came onto this one when searching for an algorithmic way to calculate workdays in Power BI. The solutions I found built on generating tables (manually or automatically). They do work. But just like asked in original question, I wanted it to be only arithmetic.

So in spite of the question being answered, if others are looking for the same as me, below is an example of how to do it without using generation of calendars. (I do use such calendars, but only for the specific red days).

Weekdays from Dec = 
    Var DaysSinceStart = calculate(DATEDIFF(DATE(2020,12,04),max('MyTable'[Date created]),DAY)*1.0)
    Var WeeksSinceStart = DaysSinceStart/7.0
    Var LeftOverWeekDays = INT((WeeksSinceStart - int(WeeksSinceStart)) * 7.0 + 0.5)
    Var ThisWeekDay = WEEKDAY(max('MyTable'[Date created]),2)
    Var SatReductionWeekDaysLeft = if(((ThisWeekDay-LeftOverWeekDays)<0),1,0)
    Var SunReductionWeekDaysLeft = if(((ThisWeekDay-LeftOverWeekDays)<-1),1,0)
    RETURN INT(WeeksSinceStart)*5 + LeftOverWeekDays - SatReductionWeekDaysLeft - SunReductionWeekDaysLeft