Task Start Date

Given a task due date and estimated hours, reverse engineer when the task needs to start.

 

Task Start Date = 
    VAR __EstimatedDays = 'Table'[Hours] / 8 + 'Table'[Hours] / 40 * 2
    VAR __EstimatedStartDate = ('Table'[Task Due Date] - __EstimatedDays) * 1.
    VAR __Table = 
        ADDCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    CALENDAR(__EstimatedStartDate,'Table'[Task Due Date]),
                    "__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
                ),
                [__IsWeekDay]=TRUE()
            ),
            "__WorkHours",8
        )
    VAR __Table2 =
        ADDCOLUMNS(
            __Table,
            "__ReverseCumulative",SUMX(FILTER(__Table,[Date] >= EARLIER([Date])),[__WorkHours])
        )
RETURN
    MAXX(FILTER(__Table2,[__ReverseCumulative] >= 'Table'[Hours]),[Date])

eyJrIjoiZWJiN2Y2N2QtZjU2ZC00Nzk4LTg2Y2YtMThhZjZhZmQzNDg1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9