Calculating Due Date excluding Weekends

[Anonymous]

Hi Friends,

Extremely stucked...

I am trying to calculate the Due Date of a ticket (Before when it has to be closed) based on the created date. Below is the requirement sample.

I have no clue on how to achieve this in PBI since I am very new to this app.

Ticket #	Ticket Type	Priority	SLA	Created	Due Date	Expected Result
1	Incident	1	2 hours	22/03/2019 23:00	?	25/03/2019 01:00
2	Incident	2	8 hours	22/03/2019 23:00	?	25/03/2019 07:00
3	Incident	3	10 Days	22/03/2019 23:00	?	05/04/2019 23:00
4	Incident	4	20 Days	22/03/2019 23:00	?	19/04/2019 23:00

[Anonymous]

Friends any suggestions please...?

[Anonymous · Accepted solution]

HI @Anonymous ,

Current power bi not support to calculate datetime value with text, please convert your SLA column to numeric value fields.(e.g. total hours)

Due date =
VAR _remainHour =
MOD ( [Total Hours], 24 )
VAR _daycount =
INT ( [Total Hours] / 24 )
VAR _dayfromhour =
INT ( DIVIDE ( HOUR ( [Created] ) + _remainHour, 24 ) )
VAR _endtime =
TIME ( MOD ( HOUR ( [Created] ) + _remainHour, 24 ), 0, 0 )
VAR _calculateEnd =
DATE ( YEAR ( [Created] ), MONTH ( [Created] ), DAY ( [Created] ) + _daycount + _dayfromhour )
VAR _offsetweekend =
IF ( WEEKDAY ( _calculateEnd, 2 ) > 5, 7 - WEEKDAY ( _calculateEnd, 2 ), 0 )
VAR _weekendcount =
COUNTROWS (
FILTER ( CALENDAR ( [Created], _calculateEnd ), WEEKDAY ( [Date], 2 ) > 5 )
)
RETURN
DATE ( YEAR ( _calculateEnd ), MONTH ( _calculateEnd ), DAY ( _calculateEnd ) + _weekendcount + _offsetweekend ) + _endtime

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, thanks for this solution! I am struggling with a similar problem, but I want to leave out holidays too in the expected lead time. I feel I am close, but I don't manage to filter my Date Table properly. I want to filter in a DataTable I created where I have a column that says if the date is a weekend or holiday (0 = Wknd/Hldy and 1 = workday).

This is what I come up with so far (I mainly used your function and addapted it to my needs). I kno the filter statement is incorrect (marked as red text). I try to filter my date table on the received date and the end date (= received date + 10 working days) on only weekend or holidays (= Wknd/Hldy = 0).

Exp_Lead_Time =

VAR _calculateEnd =

DATE ( YEAR ( Projecten[Date received]), MONTH ( Projecten[Date received]), DAY ( Projecten[Date received] ) + 10)

VAR _offsetweekend =

IF ( WEEKDAY ( _calculateEnd, 2 ) > 5, 7 - WEEKDAY ( _calculateEnd, 2 ), 0 )

VAR _weekendholidaycount =

COUNTROWS (

FILTER (CALENDAR(Projecten[Date received], _calculateEnd), Datetable[Wknd/Hldy] = 0)

)

RETURN

DATE ( YEAR ( _calculateEnd ), MONTH ( _calculateEnd ), DAY ( _calculateEnd ) + _weekendcount + _offsetweekend )

Could you help me solve this?

Thanks!

Lieselot