cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
javirmerino Helper III
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;

 

IDUrgencyHoursReceivedRequired Completion DateDueDate
74752Low4012/02/2020 08:4821/02/2020 17:0021/02/2020 17:00
74753Low4012/02/2020 08:5121/02/2020 17:0021/02/2020 17:00
74868Low4013/02/2020 14:26 20/02/2020 14:26
75118Normal2418/02/2020 15:50 21/02/2020 15:50
75140Normal2419/02/2020 09:51 24/02/2020 09:51
75170Low4019/02/2020 11:44 26/02/2020 11:44
75181Low4019/02/2020 13:15 26/02/2020 13:15
75183Very High819/02/2020 13:23 20/02/2020 13:23
75196Normal2419/02/2020 15:32 24/02/2020 15:32
75200Normal2419/02/2020 15:59 24/02/2020 15:59
75227Low4020/02/2020 11:30 27/02/2020 11:30
75230High1620/02/2020 11:43 24/02/2020 11:43
75231High1620/02/2020 11:47 24/02/2020 11:47
75232High1620/02/2020 11:56 24/02/2020 11:56
75234Low4020/02/2020 12:11 27/02/2020 12:11
75236Urgent420/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
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
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!

javirmerino Helper III
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).

javirmerino Helper III
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
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
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!

Highlighted
javirmerino Helper III
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
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!

Super User IV
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 = 
                ADDCOLUMNS(
                    __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(
                    __TargetDate = [Received],
                    [Received]+__TimeLeft,
                    __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!

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors