Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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] ?
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
)
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
)
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?
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!
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.
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
@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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |