Forum Discussion
Solved
Adding Workdays to Dates
Hi, I have a table which shows a created date for when invoices are entered into our system. I need to add workdays onto this date to get our SLA Date. I have a Date Table which has a weekday index i...
Hi Lesley1Storey ,
Don't know if you want a calculated column or a measure but you can do the following.
Create a column on your calendar table
Workday = SWITCH ( TRUE (); WEEKDAY('Calendar'[Date]) IN { 6 ; 7 }; FALSE (); TRUE () )Now add the following measure to your model:
Forecasted End Date = ---------------------------------------------------------- VAR relevantdate = SELECTEDVALUE(Invoices[Date]) --this can be replaced by TODAY() VAR workdaysremain = 15 --Can be adjusted to be another value --------------------------------------------------------- /* create a virtual date table only for working days starting from the relevant date and only for the workdays remaining */ VAR workingdateTable = TOPN ( workdaysremain; CALCULATETABLE ( 'Calendar'; 'Calendar'[Workday] = TRUE (); 'Calendar'[Date] >= relevantdate ) ) --------------------------------------------------------- /* find the maximum date in the virtual table, which will be the forecasted end date */ VAR futuredate = CALCULATE ( MAX ( 'Calendar'[Date] ); workingdateTable ) --------------------------------------------------------- RETURN futuredatethis was adapted from the post below:
Hi Lesley1Storey ,
Don't know if you want a calculated column or a measure but you can do the following.
Create a column on your calendar table
Workday =
SWITCH (
TRUE ();
WEEKDAY('Calendar'[Date]) IN { 6 ; 7 }; FALSE ();
TRUE ()
)
Now add the following measure to your model:
Forecasted End Date =
----------------------------------------------------------
VAR relevantdate =
SELECTEDVALUE(Invoices[Date]) --this can be replaced by TODAY()
VAR workdaysremain =
15 --Can be adjusted to be another value
---------------------------------------------------------
/* create a virtual date table only for working days starting from
the relevant date and only for the workdays remaining */
VAR workingdateTable =
TOPN (
workdaysremain;
CALCULATETABLE (
'Calendar';
'Calendar'[Workday] = TRUE ();
'Calendar'[Date] >= relevantdate
)
)
---------------------------------------------------------
/* find the maximum date in the virtual table, which will be
the forecasted end date */
VAR futuredate =
CALCULATE ( MAX ( 'Calendar'[Date] ); workingdateTable )
---------------------------------------------------------
RETURN
futuredate
this was adapted from the post below: