WORKDAY Function a little different

[Anonymous]

Hello all,

I have been trying to figure this one out using all the great suggestions on the subject. I feel I am very close, but cannot find it yet.

I am creating a production planner that based on a period start day (the Monday of the following week) plus a leadtime gives a plan date.

Adding this leadtime to the period start date must skip Weekends and Holidays.

I have created a 'Date' table that has a column Date[WorkingDay] that has a 1 for working and 0 for days off. The date table has dates from 1/1/2018 to 12/31/2020 with all weekends and holidays correctly identified.

I then have another table 'PlannerInfo' that lists all the product types and their leadtime. I would like to have a calculated column that shows what the calculated plan date is.

Product	Leadtime	Plan Date
Receive core/adapter	6
Clean core/adapter	5
Saw	11
Saw copper flash	21
Core Saw (Stock)	7
Mixing Core Material	12

Thank you for your help on this.

[Anonymous · Accepted solution]

Answering my own question.

I created an index on the date table that would only go up when the day was a workday.

Row Index Column =

CALCULATE( COUNTROWS('Date'), FILTER(All('Date'), 'Date'[Date] <= EARLIER('Date'[Date])), 'Date'[WorkingDay] = 1)

Then I found the first day that had a rank equal to my leadtime

PlanDate= MINX(FILTER('Date','Date'[Row Index Column] = PlannerInfo[LeadTime]), 'Date'[Date])