calculate number of working days between 2 dates

[Anonymous]

We have a table with tasks:

• Task ID
• Task Start Date
• Task End Date

We also have a table with all days in the year, indicating the date as well as attributes like day of the week

And we have a table with the holidays in the year.

We would like to calculate the number of days between End and Start date for each task. We know that we can subtract those 2 dates to get a number, but we woudl like to exclude weekends and holidays. Is there any way to do that in Power BI Desktop?

[Eric_Zhang · Accepted solution]

@Anonymous

With tables as below, you can either use a calculated column or a measure.

Total Working Days Column = 
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= Tasks[Task Start Date]
            && 'Calendar'[Date] <= Tasks[Task End Date]
    ),
    'Calendar'[isWorkDay]
)


Total Working Days Measure = 
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( Tasks[Task Start Date] )
            && 'Calendar'[Date] <= MAX ( Tasks[Task End Date] )
    ),
    'Calendar'[isWorkDay]
)

[Krijztian]

Hi Eric

This works great in the scenario where StartDate is always before EndDate... How to ensure that it still calculates correctly in cases where StartDate is after EndDate?
- currently it only gives me blank values for all of these (which makes sense), but what I would like to measure this same distance, but in the opposite direction?

[kasiaw29]

Hey @Eric_Zhang 

I tried your solution as a calulated column ( I already had a date dimension build in my report so re-used it).

For some reason when I use your solution I'm getting 1 where I'd expect a 0 when start date and finish date happen to be on the same day? I might be wrong?

I'm using your solution to check how many working days an activity is early or late by and in above snapshot, if you look at a seconds line for example, activity was completed on time but I get 1 indicating it's a day late. 

Any suggestions for this kind of issue? 

Thanks

[Anonymous]

Probably easiest to add a calc column to your date table IsWorkDay (which uses LOOKUPVALUE into your holidays table and Sat/Sun sorta thing).

Here is a totally un-tested measure that should probably work for you:

Total Working Days = CALCULATE (
        COUNTROWS(Calendar),
        Calendar[IsWorkDay] = TRUE( ),
        Calendar[Date] >= MIN(Tasks[Task Start Date]),
        Calendar[Date] <= MAX(Tasks[Task End Date]
   )

[KGrice]

Hi @Anonymous. I was trying to figure this one out too, and tested your measure for my own learning. It got me in the right direction, but it needs a FILTER. As is:

@Anonymous. With just a little modification, you'll get the result you want: 

Total Working Days = CALCULATE(
			COUNTROWS('Calendar'),
			Calendar[IsWorkday] = TRUE(), 
			FILTER(ALL('Calendar'), (Calendar[Date] >= MIN(Tasks[Start Date])) 
				&& (Calendar[Date] <= MAX(Tasks[End Date]))
				)
			)

FYI: When using Calendar as a name for a table, it looks like you have to wrap it in single quotes when using it without a column reference.

If the IsWorkday column doesn't make sense, it should look something like the photo below, where there is a relationship between the Calendar table and Holidays table based on the Date.