How to determine the end date excluding weekends/holidays where I have the start date and duration

[SirTim]

Hi Community,

I am wanting to determine the end date of a task excluding weekends and holidays. I have the start date and duration. I have trawled the forum and have not been able to find a solution.

I have three tables: Tasks, Date, Public Holiday. The result I am looking for is if a task should start on a Friday and has a duration of 3 days then the end date should be Tuesday of the following week. For the life o me I cannot seem to finds a solution that works. Any help would be greatly appreciated.

[v-yiruan-msft · Accepted solution]

Hi @SirTim ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create two calculated columns as in Dates table as below to get the flag of isHoliday and Weekday

Note: If you have already created these two columns, you can skip this step and go to the next step directly: create the calculated column EndDate.

isHoliday = 
VAR _holidaydate =
    CALCULATE (
        MAX ( 'PublicHolidays'[Date] ),
        FILTER ( 'PublicHolidays', 'PublicHolidays'[Date] = 'Dates'[Date] )
    )
RETURN
    IF ( ISBLANK ( _holidaydate ), 0, 1 )

isWorkingday = IF('Dates'[isHoliday]=0&&'Dates'[Weekday]<6,1,0)

2. Create a calculated column in Tasks table to get the EndDate

EndDate = 
VAR _tab =
    ADDCOLUMNS (
        'Dates',
        "@count",
            CALCULATE (
                COUNT ( 'Dates'[Date] ),
                FILTER (
                    'Dates',
                    'Dates'[isHoliday] = 0
                        && 'Dates'[Weekday] < 6
                        && 'Dates'[Date] >= 'Tasks'[StartDate]
                        && 'Dates'[Date] <= EARLIER ( 'Dates'[Date] )
                )
            )
    )
RETURN
    CALCULATE (
        MIN ( 'Dates'[Date] ),
        FILTER (
            'Dates',
            MAXX ( FILTER ( _tab, [Date] = EARLIER ( 'Dates'[Date] ) ), [@count] ) = 'Tasks'[Duration]
        )
    )

Best Regards

[SirTim]

@v-yiruan-msft If I have a task that ends on a Friday, it is showing that task a completing on a Sunday. How do I resolve this. it should show the task as being complete on the Firday.

[mahoneypat]

You can modify the approach described in this video to get your result.

(1) Power BI - Tales from the Front #03 - Due Date From Working Days - YouTube

Pat

[Jihwan_Kim]

Hi,

My sample only has weekends, and I do not have your public holiday information.

However, I think the concept of approaching the problem is quite similar.

Please check the link down below, that is the sample of the pbix file.

Enddate excluding weekend count : =

VAR _startdate =

MAX ( Tasks[Startdate] )

VAR _duration = [Duration measure :]

VAR _calendarexcludeweekend =

FILTER (

'Calendar',

'Calendar'[DayOfWeek] <> 0

&& 'Calendar'[DayOfWeek] <> 6

&& 'Calendar'[Date] >= _startdate

)

VAR _rankexcludeweekend =

FILTER (

ADDCOLUMNS (

FILTER (

'Calendar',

'Calendar'[DayOfWeek] <> 0

&& 'Calendar'[DayOfWeek] <> 6

&& 'Calendar'[Date] >= _startdate

),

"@rank", RANKX ( _calendarexcludeweekend, CALCULATE ( MAX ( 'Calendar'[Date] ) ),, ASC )

),

[@rank] = _duration

)

RETURN

MAXX ( _rankexcludeweekend, 'Calendar'[Date] )

Link to the sample pbix file 

[SirTim]

Hi Jihwan,

Thanks for the prompt response.

Unfortunately I don't get the same response as you: I have duration (dev days captured), however, no end date is calculated. Any suggestions?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Sorry that I cannot know where is the problem without seeing how you wrote a measure in what data model. Showing only the screenshot does not help me to understand.

That is why I created the sample by myself and shared it.

I am not sure how your data model looks like. I am not sure how you applied my measure to your data model.

Sharing your sample pbix file will help people to look into it to come up with a more accurate solution.

Thanks.