topic Re: Trying to calculate a date based on working days from a date that is eneterd in DAX Commands and Tips

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

Anonymous — Wed, 11 Jan 2023 16:33:46 GMT

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

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

Greg_Deckler — Wed, 11 Jan 2023 16:45:16 GMT

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

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

Anonymous — Thu, 12 Jan 2023 05:52:04 GMT

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.

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

Anonymous — Wed, 18 Jan 2023 14:38:07 GMT

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?

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

Anonymous — Wed, 18 Jan 2023 14:46:07 GMT

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

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

Anonymous — Thu, 19 Jan 2023 01:14:15 GMT

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.