Forum Discussion

lloydchughes's avatar
lloydchughes
New Member
4 years ago
Solved

Create Task EndDate but exclude weekends

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.

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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] )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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] )
  • lloydchughes's avatar
    lloydchughes
    New Member
    4 years ago

    This solution works well thank you. 

     

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

     

    Cheers,

     

    Lloyd

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      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.