cancel
Showing results for
Did you mean:
Regular Visitor

## calculate number of working days between 2 dates

We have a table with tasks:

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: calculate number of working days between 2 dates

@broekman

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

```Total Working Days Column =
SUMX (
FILTER (
'Calendar',
),
'Calendar'[isWorkDay]
)

Total Working Days Measure =
SUMX (
FILTER (
'Calendar',
),
'Calendar'[isWorkDay]
)```
3 REPLIES 3
Senior Member

## Re: calculate number of working days between 2 dates

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( ),
)```

Established Member

## Re: calculate number of working days between 2 dates

Hi @scottsen. 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:

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

```Total Working Days = CALCULATE(
COUNTROWS('Calendar'),
Calendar[IsWorkday] = TRUE(),
)
)```

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.

Moderator

## Re: calculate number of working days between 2 dates

@broekman

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

```Total Working Days Column =
SUMX (
FILTER (
'Calendar',
),
'Calendar'[isWorkDay]
)

Total Working Days Measure =
SUMX (
FILTER (
'Calendar',
),
'Calendar'[isWorkDay]
)```

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 166 members 1,612 guests
Recent signins: