Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

WORKDAY Function a little different

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.

 

ProductLeadtimePlan Date
Receive core/adapter6 
Clean core/adapter5 
Saw11 
Saw copper flash21 
Core Saw (Stock)7 
Mixing Core Material12 

 

Thank you for your help on this.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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])
     
     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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])