Trying to calculate a date based on working days from a date that is eneterd

[Anonymous]

Hi All,

I am trying to find a way to calculate a date that is 22 working days past a date that is entered into a table.

Need to make sure that weekends and federal holidays are not part of that 22 day count.

I tried using NETWORKDAYS, but that option is not available in the environment I am working in.

Example;

Date entered 12/31/22  + 22 working days = 2/2/23.

I need to calculate with a formula that will produce results for every day entered

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

I make an example on Calendar table:

1.Create a Calendar table, it includes weekday column:

Calendar = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),
"Weenday",WEEKDAY([Date],2))

2.Create a holiday table to input the holiday and related date

3.Then create the inputdate measure and the outputdate measure:

Outputdate = MAXX(TOPN(23,FILTER(ALL('Calendar'),[Date]>[Input_date]&&[Weenday]<6&&[Date]<>MAX(Holiday[Date])),[Date],ASC),[Date])

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

And how do I get it to work for the next 10 years?  Do I have to build a table that has all holidays for the next 10?

[v-xinruzhu-msft]

Hi @Anonymous 

Based on the solution in offered, you need to create a table to store the holiday.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I need to calculate a date that is 45 work days past the start date.  I have to account for all weekends and holidays, then give users a deadline for them to be finished with the task which is 45 work days after it was entered in the system

[Greg_Deckler]

@Anonymous You could try the old school method: Net Work Days - Microsoft Power BI Community