topic Determining the Completion Date Based on the Start Date + <xx> Working Days in DAX Commands and Tips

Determining the Completion Date Based on the Start Date + Working Days

Rosh88 — Mon, 01 Apr 2024 07:56:04 GMT

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

Re: Determining the Completion Date Based on the Start Date + Working Days

some_bih — Mon, 01 Apr 2024 09:03:15 GMT

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

Re: Determining the Completion Date Based on the Start Date + Working Days

talespin — Mon, 01 Apr 2024 12:29:12 GMT

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

Re: Determining the Completion Date Based on the Start Date + Working Days

gmsamborn — Wed, 10 Apr 2024 15:54:37 GMT

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