cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Eric_Zhang
Microsoft
Microsoft

@Anonymous

 

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

View solution in original post

5 REPLIES 5
Eric_Zhang
Microsoft
Microsoft

@Anonymous

 

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

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?

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?

kasiaw29_0-1599664248968.png

 

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

 

scottsen
Memorable Member
Memorable Member

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

 

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

 

 

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

 

IsWorkday.PNG

 

 

 

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!