cancel
Showing results for
Did you mean:
Helper III

## Calculate StartDate + Working hours to determine ClosedDate

Hi all, please excuse brevity.

How do i calculate an expected DueDate based on a StartDate and a predetermined Duration field? I have a date dimensions table with IsWeekend and IsHoliday columns that can be referenced buit also need to consider working hours (standard 09:00-17:00, Monday-Friday excl. Bank Holidays).

I've given a brief example below of what i'm trying to achieve, with a number of possible circumstances.

IDs 74752 & 74753 for example, have a specified Required Completion Date which overrides any calculation.

ID 74868 doesnt have a specified Required Completion Date so has a calculated DueDate of Received + 40 working hours (determined by the Urgency) to give a DueDate of 20/02/2020 14:26.

The remaining records show the other possibel Hours to complete which would need to be referenced;

 ID Urgency Hours Received Required Completion Date DueDate 74752 Low 40 12/02/2020 08:48 21/02/2020 17:00 21/02/2020 17:00 74753 Low 40 12/02/2020 08:51 21/02/2020 17:00 21/02/2020 17:00 74868 Low 40 13/02/2020 14:26 20/02/2020 14:26 75118 Normal 24 18/02/2020 15:50 21/02/2020 15:50 75140 Normal 24 19/02/2020 09:51 24/02/2020 09:51 75170 Low 40 19/02/2020 11:44 26/02/2020 11:44 75181 Low 40 19/02/2020 13:15 26/02/2020 13:15 75183 Very High 8 19/02/2020 13:23 20/02/2020 13:23 75196 Normal 24 19/02/2020 15:32 24/02/2020 15:32 75200 Normal 24 19/02/2020 15:59 24/02/2020 15:59 75227 Low 40 20/02/2020 11:30 27/02/2020 11:30 75230 High 16 20/02/2020 11:43 24/02/2020 11:43 75231 High 16 20/02/2020 11:47 24/02/2020 11:47 75232 High 16 20/02/2020 11:56 24/02/2020 11:56 75234 Low 40 20/02/2020 12:11 27/02/2020 12:11 75236 Urgent 4 20/02/2020 12:53 20/02/2020 16:53

Any idea how to begin tackling this one? I've been researching for several months and the only similar solutions i can find all involve having a predetermined completion datetime.

18 REPLIES 18
Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

@javirmerino one way could be to add custom column in Power Query  and call it Due Date

``= if [Required Completion Date] = null then [Received] + #duration(0,[Hours],0,0) else [Required Completion Date]``

Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

I've written a number of articles and such on aggregation duration.

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helper III

## Re: Calculate StartDate + Working hours to determine ClosedDate

Hi @parry2k , does that take into consideration working hours and weekends/bank holidays?

If it was a simple +40 hours then i'd accept that as the solution, but it should be Received + Duration - (weekend days + holiday days + Out Of Office Hours), which i dont think your query accounts for?

Using 13/02/2020 14:26 as an example, the DueDate should be 20/02/2020 14:26 (40 working hours later) not 15/02/2020 06:26 (40 hours later).

Helper III

## Re: Calculate StartDate + Working hours to determine ClosedDate

Thanks as always, @Greg_Deckler . I'll have a read through and see if and how i can apply this.

At first glance it doesn't look to resolve the problem around hours vs. working hours, but i'm sure i'll need to reference it at some point!

Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

You know, now that I think about it, can you just use your date + 1/24*[Hours] ?

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

I also have these for net work days and net work duration:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration/m-p/481543#M182

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helper III

## Re: Calculate StartDate + Working hours to determine ClosedDate

If only! Using the example form a couple of posts up;

"Using 13/02/2020 14:26 as an example, the DueDate should be 20/02/2020 14:26 (40 working hours later) not 15/02/2020 06:26 (40 hours later)."

i'll check out the other references to see if that helps, also.

Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

What I would do is in your date table, create a column that holds the number of working hours for each day. Holidays and weekends would be 0, other days would be 8. This will make the entire thing much easier because you can essentially setup a psuedo while loop to "count down" the days until your number of Hours is used up. I've done a bunch of these kinds of calculations. I'll try to mock one up.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Super User IV

## Re: Calculate StartDate + Working hours to determine ClosedDate

OK @javirmerino attached a PBIX for you. I think this handles all of the boundary cases. See attached PBIX.

``````DueDate =
IF(
NOT(ISBLANK([Required Completion Date])),
[Required Completion Date],
VAR __HourTime = 1/24*[Hours]
VAR __Dates = FILTER('Calendar',[Date]>=[Received].[Date])
VAR __Table =
__Dates,
"__WorkTime",SUMX(FILTER(__Dates,[Date]<=EARLIER([Date])),[Working Time])
)
VAR __TargetDate = MINX(FILTER(__Table,[__WorkTime] >= __HourTime),[Date])
VAR __TargetDateTime = __TargetDate + 1/24*9 //start at 9AM
VAR __TimeLeft = MAXX(FILTER(__Table,[Date]=__TargetDate),[__WorkTime]) - __HourTime
VAR __Target =
IF(
__TargetDateTime + __TimeLeft
)
VAR __FinalTarget =
IF(
HOUR(__Target) > 17 || (HOUR(__Target)=17 && MINUTE(__Target) > 0),
__TargetDateTime + 1 + (__Target - (__TargetDateTime + 1/24*8)),
__Target
)
RETURN
__FinalTarget
)``````

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors