Determining the Completion Date Based on the Start Date + <xx> Working Days

[Rosh88]

Hi, 
I am currently working with two specific tables: a Fact Table and a Calendar Table. My objective is to accurately calculate the completion date for each entry. The formula to determine the completion date is: Start Date + Number of Working Days.

Fact Table

Calender Table

For example, if the start date is 2017/01/01(1st Jan 2017), then the completion date should be 2017/01/06.
Similarly, for a start date of 2017/01/02, the completion date should be 2017/01/09

I seek guidance on how to implement this calculation effectively. Any assistance or advice you can provide would be greatly appreciated.

Thank you for your support.

Best,
Rosh

[talespin · Accepted solution]

hi @Rosh88 

I have a disconnected CALENDAR Table, please note that I have True/False(Boolean) in IsWorkingDay, you can change DAX to 'CALENDAR'[IsWorkingDay] = 1 instead of just  'CALENDAR'[IsWorkingDay]

This is a calculated column

CompletionDate =

VAR _StartDate = 'Fact'[StartDate]

VAR _WorkingDays = 'Fact'[WorkingDays]

VAR _FltrTable = FILTER('CALENDAR', 'CALENDAR'[IsWorkingDay] && 'CALENDAR'[Date] >= _StartDate)

VAR _WorkingDaysTbl =

ADDCOLUMNS(

            _FltrTable,

            "@RANK",

            RANK(DENSE, _FltrTable, ORDERBY('CALENDAR'[Date]))

        )

RETURN MAXX( FILTER(_WorkingDaysTbl, [@RANK] = _WorkingDays), [Date])

[gmsamborn]

Hi @Rosh88 

I came up with a solution adding a [WorkdayOffset] column to my Date table.

First I added a [IsWeekday] calculated column to my Date table.

IsWeekday = IF( WEEKDAY( [Date], 2 ) > 5, 0, 1 )

Then I added my [WorkdayOffset] column to my Date table.  This is to keep track of workdays.

WorkdayOffset = 
VAR _Curr = [Date]
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[Date],
            'Date'[IsWeekday]
        ),
        'Date'[IsWeekday] = 1
    )
VAR _Min =
    IF(
        _Curr < TODAY(),
        _Curr,
		TODAY()
    )
VAR _Max =
    IF(
        _Curr < TODAY(),
        TODAY(),
		_Curr
    )
VAR _Count =
    COUNTROWS(
        FILTER(
            _Table,
            [Date] >= _Min
                && [Date] <= _Max
        )
    ) - 1
VAR _Logic =
    IF(
        _Curr >= TODAY(),
        _Count,
        _Count * -1
    )
RETURN
    _Logic

Finally, I added a [Completion Date] calculated column to the main table.

Completion Date = 
VAR _Start = [StartDate]
VAR _WorkDays = [WorkingDays]
VAR _StartOffset = 
    CALCULATE(
        MAX( 'Date'[WorkdayOffset] ),
        'Date'[Date] = _Start
    )
VAR _End =
    CALCULATE(
        MAX( 'Date'[Date] ),
        'Date'[WorkdayOffset] = _StartOffset + _WorkDays - 1
    )
RETURN
    _End

Let me know if you have any questions.

Completion Date.pbix

[some_bih]

Hi @Rosh88 calculation logic should include Item column as you want to know Comp.Date based on Item column also?