cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
broekman Regular Visitor
Regular Visitor

calculate number of working days between 2 dates

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
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.

Capture.PNG

 

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]
)
3 REPLIES 3
scottsen Senior Member
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( ),
        Calendar[Date] >= MIN(Tasks[Task Start Date]),
        Calendar[Date] <= MAX(Tasks[Task End Date]
   )

 

KGrice Established Member
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:

 

NoFILTER.PNG

 

 

@broekman. 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.

 

IsWorkday.PNG

 

 

 

 

 

Moderator Eric_Zhang
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.

Capture.PNG

 

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]
)

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 166 members 1,612 guests
Please welcome our newest community members: