Add working days to a date

Question

In my powerpivot model there are two related tables, one of them a time table. In the time table is a field 'workingday' that contains a 0 (not a working day) or a 1 (working day). Also the holidays in my country are flagged as a workingday 0 in the time table.

The other (task)table contains two fields, a startdate and a number of days someone is allowed to complete a task.

What I want to do is add the number of days to the startdate in the tasktable, but only taking in account the working days as flagged in the time-table.

Example: 31 dec + 1 working day = 2 jan

I have a feeling there must be a simple answer, but somehow I am lost on this.

Answer 1

I'm not exactly clear on what you are trying to accomplish here but it sort of sounds like you want to change the startdate for a task if it falls on a workingday=0 to the first available working day?

You should be able to get there using FIRSTDATE and CALCULATETABLE perhaps. So, something along the lines of:

IF(Dates[workingday]=1,startdate,FIRSTDATE(CALCULATE(VALUES(Dates[Date]),Dates[workingday]=1,Dates[Date]>startdate)))

Basically, return the first date from the [Date] column in Dates table that is filtered down to workingday=1 and a date > the startdate. I did not build the model and test this formula.

Answer 2

Actually not very simple.

=MINX(
	FILTER(
		DimDate
		,DimDate[Date] > EARLIER( Task[StartDate] )
			&& CALCULATE(
					SUM( DimDate[WorkingDay] )
					,DATESBETWEEN(
						DimDate[Date]
						,EARLIER( Task[StartDate] ) + 1
						,DimDate[Date]
					)
				) = EARLIER( Task[DaysToComplete] )
	)
	,DimDate[Date]
)

MINX() takes a table and an expression. It evaluates the expression in its second argument for each row in the input table, and returns the minimum value across all those rows.

Now let's get to the meat of this guy, the FILTER().

FILTER() takes a table and creates a row context by iterating through each row of that table. It evaluates its second argument in each row context of that iteration. It returns a table composed only of those rows for which the second argument returns true.

The table we filter is DimDate. What's the logical condition we evaluate? It's a combination of two conditions combined with the logical and operator, &&.

The first condition that must be true is that the value of DimDate[Date] on the current row of FILTER()'s iteration must be strictly greater than the value of EARLIER( Task[StartDate] ). EARLIER() allows us to refer to an outer row context. We have a row context from Task, the table where this calculated column is evaluated. The FILTER() row context exists within this outer row context. EARLIER() refers to the start date of the current row in iteration through Task.

So we have dates greater than Task[StartDate]. Now we need to test the other half of that logical and.

CALCULATE() takes an expression to evaluate in its first argument. It takes a series of arguments after that to create a filter context in which to evaluate the first expression.
Our expression is SUM( DimDate[WorkingDay] ). The filter context we set up is evaluated once for every row in DimDate.

DATESBETWEEN() takes a reference to a column that contains contiguous, nonrepeating dates, and returns all dates between (inclusive) a start date and end date passed as arguments. The start date we pass is again EARLIER( Task[StartDate] ), but with 1 added (this is to exclude [StartDate] itself, because DATESBETWEEN() uses inclusive endpoints).

The end date for DATESBETWEEN() is DimDate[Date]. This is evaluated in the current row context of FILTER()'s iteration. Thus, for every row in DimDate we create a DATESBETWEEN() with a start date of Task[StartDate] and an end date of DimDate[Date]. These many filter contexts are used to evaluate SUM( DimDate[WorkingDay] ).

SUM( DimDate[WorkingDay] ) should be equal to the days we expect the task to take - referenced with EARLIER( Task[DaysToComplete] ).

This FILTER() will return the first date that is a valid end-date based on [DaysToComplete] as well as an adjacent, contiguous range of dates strictly greater than that date that have [WorkingDay] = 0. If a Friday is a valid end date, Saturday and Sunday would be included in the output of FILTER() as well.
Thus, the MINX() to get the first such date.

Screen with sample model and data. There's an active relationship between Task[StartDate] and DimDate[Date], but that is unnecessary for this to work.

---

GNet Group BI Consultant

Answer 3

Thank you for the quick and extensive responses. Sorry I wasn't clear about what I was trying tot build. Our user wants to make a report of all the tasks containing the startdate, number of days that is allowed to complete the task and a new calculated field 'enddate' that is based on the first two fields, but only counting working days.

The above looks very prommissing I will try this today.

Thanks.

** update: Implemented the solution of Greg2178, works perfectly!