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

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

     

3 Replies

  • talespin's avatar
    talespin
    Solution 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
      Super 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

       

       

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

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