Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This looks to be pretty much what i need; the only changes i need to make now are around the start time of the "timer"; from the look of your code, its hard-coded to start from 9am regardless.

 

If its received in working hours then the time should start from then, but if its out of hours then it should start from 9am the next working day...

@javirmerino  - OK, minor change required. I think this covers your additional use case now. Updated PBIX attached.

 

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 = 
                IF(
                    HOUR([Received]) > 17 || (HOUR([Received])=17 && MINUTE([Received]) > 0),
                    __TargetDate + 1 + 1/24*9, //start at 9AM,
                    __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
    )

  


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler . I've been giving your previous code a go, but it's freezing my pc each time! I've now created an extract version to test it on though as i'm hoping its something to do with the directquery connection...

i'll try this out too.

Ah, well I was not focused on making this Direct Query friendly. I'll have to check to make sure that all of the functions are compatible with Direct Query. Also, I would have to take a critical eye towards this with respect to performance. There is lots of iteration going on here. How many rows are we talking?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Around 10,000 rows per year, give or take. Not huge volumes but obviously enough if theres several iterations happening each time.

It's frozen as an extract as well now, though!

I'll have to take a look, from what I recall that should be accounted for but will have to confirm that. Will have to fire up the pbix and put that use case in. If it is a problem, shouldn't be that big of a deal to account for it.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler , i'll give that a go this morning and will let you know my outcome.

 

The last challenge will be for me to resolve the start point of requests raised outside of office hours; unless this will account for that also?

@javirmerino did you tested what I mentoned. Adding custom column in query editor? If yes, what is the issue with it?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@javirmerino I just read your reply to my original answer and I see where my solution wouldn't work as you want to consider only working hours. I guess @Greg_Deckler  is always on it with you. You are in safe hands!! 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
parry2k
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.