Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 included with days 1-5 (saturday and sunday included as day 5). I need to add the workdays onto the created date. The two tables are connected by created date from the Invoices file and Dates from the Date table. Any help would be greatly appreciated.
Solved! Go to Solution.
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |