Create Task EndDate but exclude weekends

[lloydchughes]

Hi,

I'm trying to create an End Date for proejct task from StartDate + BuildTimeDays  (Which is just a whole number)

I want to exclude the weekends from the calculation and be able to display them in the gantt chart visual.

Example -

Task 1 - StartDate 01/01/2023, BuildTimeDays = 15 meaning EndDate would be 06/01/2023 normally but 01/01/2023, 11/01/2023 and 07/01/2023 being a Saturday EndDate would be 16/01/2023 but really it should be Thursday 21/01/2023.

It seems this should be pretty easy in a custom column calculation but i'm rather new to PowerBi.

Any assistance would be great thank you.

[lloydchughes]

This solution works well thank you. 

How would you do it if you were using a Date Table?

Cheers,

Lloyd

[Jihwan_Kim]

Hi,

Thank you for your message.

If there was a calendar table, I can save some lines in the formula to create virtual calendar table, and then I can refer to the actual calendar table in the formula.

Please share your sample pbix file's link, and then I can try to come up with another solution.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

If you do not have a calendar dim table, please check the below picture and the attached pbix file.

It is for creating a calculated column.

Enddate CC =
VAR _mindate =
    MIN ( Data[StartDate] )
VAR _maxdate =
    DATE ( YEAR ( MAX ( Data[StartDate] ) ) + 1, 12, 31 )
VAR _calendartableexcludingsatsun =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( _mindate, _maxdate ),
            "@dayname", FORMAT ( [Date], "ddd" )
        ),
        NOT ( [@dayname] IN { "Sat", "Sun" } )
    )
VAR _count =
    ADDCOLUMNS (
        _calendartableexcludingsatsun,
        "@count",
            COUNTROWS (
                FILTER (
                    _calendartableexcludingsatsun,
                    [Date] >= Data[StartDate]
                        && [Date] <= EARLIER ( [Date] )
                )
            )
    )
RETURN
    SUMMARIZE ( FILTER ( _count, [@count] = Data[BuildTimeDays] ), [Date] )