Production schedule Start date and end date automate

[Anonymous]

I want to create a dynamic Start date and End date column. In my excel sheet only the start date of the first job will be provided and the number of production days for each job after that will be available. 

This dataset is constantly changing- Sometimes new items are inserted between exitsting item or an exisitng item is moved up the row by cut copying and inserting in the excel file. 

Requirement

start date of all consecutive row to be populated as Start date=(End date of previous row+ 1) exclude weekends and list of holidays.

end date for each row End date=(Start date+ number of production days)

Also then 5 columns for 5 working days of the week need to be added which is shaded based on production date and day of each item.

Given,

First Start date

Number of production days 

Need,

Start date of Consecutive rows

All end date 

Shading to block busy days of the week 

Sample excel  

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You try this way as below:

Step1:

Create a date table that excludes weekends and list of holidays or do a flag for them.

Step2:

Add an index column in data table.

Step3:

Then use this logic formula to create the schedule Start date and end date column

schedule Start date = var firststartdate=CALCULATE(MAX('Table'[Sched Start]),FILTER('Table','Table'[Index]=1)) return
var days=CALCULATE(SUMX('Table','Table'[# of Production days ]+1),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])))+0  return
var _table=TOPN(days+1,FILTER('Date','Date'[Date]>=firststartdate),[Date],ASC) return
MAXX(_table,[Date])

schedule End date = var firststartdate=CALCULATE(MAX('Table'[Sched Start]),FILTER('Table','Table'[Index]=1)) return
var days=CALCULATE(SUMX('Table','Table'[# of Production days ]+1),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])))+0  return
var _table=TOPN(days+1+'Table'[# of Production days ],FILTER('Date','Date'[Date]>=firststartdate),[Date],ASC) return
MAXX(_table,[Date])

Result:

and here is sample pbix file, please try it.

Also for Monday to Friday, you could refer this post:

https://community.powerbi.com/t5/Desktop/putting-time-intervals-into-bins/td-p/842383

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

The solution is perfect. It works just how i wanted. Brilliant!

Would you have any advice or assistence for the Conditional formatting based shading?

Basic:

The conditional formating needs to be done for each column based on the start and end date. Eg- For a item running from monday to friday- Shade Mon, Tue, Wed, Thu, Fri etc.

Advnaced:

For the ones highlighted in red you will see that the item is planned to run for more than a week (the week numbers are different and the number of production days are more than 5) the item needs to reflect in the adjacent row as well to allow for shading on the days of next week as well. 

How to approch both the basic and the advanced conditional formatting mentioned above?