Auto increment a measure in Power BI

[heathernicole]

Here's the dataset:

1)Event Date       2)Factory Scheduled Date  3)Auto Scheduled date     4)Combined Column

12/29/2017	2/16/2018		2/16/2018
12/29/2017	2/23/2018		2/23/2018
12/29/2017	2/23/2018		2/23/2018
12/29/2017	2/23/2018		2/23/2018
12/29/2017	2/23/2018		2/23/2018
	2/23/2018		2/23/2018
12/29/2017	3/2/2018		3/2/2018
12/29/2017	3/2/2018		3/2/2018
12/29/2017	3/2/2018		3/2/2018
12/29/2017	3/2/2018		3/2/2018
12/29/2017		3/9/2018	3/9/2018
12/29/2017		3/9/2018	3/9/2018
12/29/2017		3/16/2018	3/16/2018
12/29/2017		3/16/2018	3/16/2018
12/29/2017		3/23/2018	3/23/2018
12/29/2017		3/23/2018	3/23/2018
12/29/2017		3/23/2018	3/23/2018
12/29/2017		3/30/2018	3/30/2018
12/29/2017		3/30/2018	3/30/2018

Process:

• The Event Date Column sets the priority
• The Factory Scheduled date is just that - the date the factory schedules the order to be completed
• If there is no factory date scheduled the 'Auto-scheduled' date kicks in to help the factory "predict" when it will be done with it's orders.

I have this formula:

Scheduling Group Column =
IF (
    ' Data [Running Total COLUMN] = 0,
    BLANK (),
    ( INT ( DIVIDE ( ' Data '[Running Total COLUMN], 33000 ) + 9 ) )
)

That gives each order a Scheduling group number. The '9' in that formula is what I need to "auto-increment' based on the following:

It's tied to this 'Week End Scheduler' table (date table)

Date	Index
1/12/2018	1
1/19/2018	2
1/26/2018	3
2/2/2018	4
2/9/2018	5
2/16/2018	6
2/23/2018	7
3/2/2018	8
3/9/2018	9
3/16/2018	10
3/23/2018	11
3/30/2018	12
4/6/2018	13
4/13/2018	14
4/20/2018	15
4/27/2018	16
5/4/2018	17
5/11/2018	18
5/18/2018	19
5/25/2018	20
6/1/2018	21

So if the LAST factory scheduled date is 3/2/2018 (as in the sample set) - I need that formula to 'auto-schedule' to the NEXT week's date (3/9/2018). In this case the '9' in the measure posted above forces the increment to the next date available. It's tied to the Id column in the Week End scheduler table. 

But I currently manually have to adjust it. I'm trying to figure out how to automatically look at the next dateand then start auto-scheduling for the following week's date.

My first thought was a nested if or switch of some sort - but I don't want to mess up the math that's going on in that measure. Any ideas in the right direction would be extremely helpful.

[Anonymous]

@heathernicole,

I am not quite clear about your logic. Could you please tell us the relationships among the three tables you mentioned(sample table, Data, Week End schedule)? What is the sample data of Running Total COLUMN and what is your expected result for Scheduling Group Column based on the above sample data?

Regards,
Lydia