Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I need to calculate a Due Date column based on a given date and working days.
I am working with tickets issue in a given date-time that have to be completed before a due date based on different OLAs. I am having issues trying to calculate the Due Date for those tickets with OLAs 1, 3, 5 and 7 working days.
As an example, if I am getting a tickets issued on Wednesday 25/07/2018 11:00 with an OLA of 3 working days, it has to be completed by Monday 30/07/2018 17:00 (Close of business hours).
I have seen several post about counting working days between two dates based on a date table, but I don't know how to apply the same logic to get the Due Date column based on a fix number of working days.
Hope you can guide me to the right solution.
Much appreciated.
Vaillo
Hi @Vaillo,
I'd like to suggest you use countrows with calendar functions to create variable calendar table to calculate working days.
Calculate column sample:
working days except weekend = VAR endDate = IF ( Table[End] <> BLANK (), Table[End], TODAY () ) VAR _calendar = FILTER ( CALENDAR ( Table[Start], endDate ), WEEKDAY ( [Date], 2 ) <= 5 ) //calendar wihtout weekend VAR _holidayList = VALUES ( Holiday[Date] ) //holiday date list RETURN IF ( COUNTROWS ( _holidayList ) > 0, COUNTROWS ( EXCEPT ( _calendar, _holidayList ) ), COUNTROWS ( _calendar ) )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Thank you for your answer.
That's not exactly what I am looking for. You are calculating the number of working days based in an end date. I need to do the opposite, calculate the end date based on the number of working days.
My scenario is the following: I get jobs issued with different OLAs (1, 3, 5 or 7 working days), so I need to calculate the date when the jobs are due to organise the workforce to meet as many OLAs as possible. So I need to create a calculation that given the date the job was issued and the number of working days of its OLA, I could get the date the job is due:
Due Date = fx ( Date issued, Number of working Days)
Best regards,
Vaillo
hi
actually i was looking the same thing . i wanted to caculate the due date based in working days.
Hi @Vaillo,
So you mean you want to calculate max deadline based on current date and working days?
If this is a case, you can try to use below formula:
Max available deadline = VAR _workingDays = xxxx VAR _nonWorkingDays = COUNTROWS ( FILTER ( CALENDAR ( Table[Start], Table[Start] + _workingDays ), WEEKDAY ( [Date], 2 ) > 5 ) ) RETURN Table[Start] + _workingDays + _nonWorkingDays
Regards,
Xiaoxin Sheng
are you working days mon-fri?
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.
Yes, and I have to take into account public holidays as well (by state), so I have a date table with the working days. The issue is that I don't know how to use the table to be able to count 1,3,5 or 7 business dates to get the Due Date I need.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |