Forum Discussion

Rosh88's avatar
Rosh88
New Member
2 years ago
Solved

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

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 comp...
DAX
powerbi
WorkingDays
  • talespin's avatar
    talespin
    2 years ago

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

     

talespin's avatar
talespin
Icon for Solution Sage rankSolution Sage
2 years ago

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's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

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