Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lesley1Storey
New Member

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a5b04c5c-...

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=a5b04c5c-...

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.