schedule group quantities by week ending date measure?

[heathernicole]

This is "Part 2" of this original problem. @Sean came up with a beautiful (and simple solution) to create Groups.

Sample Data:

Event Date      Item #	 SO #	  PO #	     Open QTY.	RunTot	Index	Scheduling Group
7/5/2017	61303 D	242553	15678718	2100	2100	1	Group 1
7/5/2017	61308 D	242553	15678718	600	2700	2	Group 1
7/5/2017	61304 D	242553	15678718	2400	5100	3	Group 1
7/5/2017	61330 D	242553	15678718	13200	18300	4	Group 1
7/5/2017	61322 D	242553	15678718	7800	26100	5	Group 1
7/5/2017	61310 D	242553	15678718	600	26700	6	Group 1
7/5/2017	61302 D	242553	15678718	300	27000	7	Group 1
7/5/2017	61325 D	242553	15678718	4200	31200	8	Group 1
7/5/2017	61104 D	242553	15678718	4200	35400	9	Group 1
7/5/2017	61311 D	242553	15678718	600	36000	10	Group 1
7/5/2017	61324 D	242553	15678718	6600	42600	11	Group 2
7/5/2017	61326 D	242553	15678718	600	43200	12	Group 2
7/5/2017	61314 D	242553	15678718	1800	45000	13	Group 2
7/5/2017	61312 D	242553	15678718	3600	48600	14	Group 2
7/5/2017	61320 D	242553	15678718	1800	50400	15	Group 2
7/5/2017	61329 D	242553	15678718	8400	58800	16	Group 2
7/5/2017	61323 D	242553	15678718	2700	61500	17	Group 2
7/5/2017	61315 D	242553	15678718	10200	71700	18	Group 2
7/5/2017	61319 D	242553	15678718	4800	76500	19	Group 2
7/5/2017	61321 D	242553	15678718	2700	79200	20	Group 2
7/13/2017	61313 D	242610	15678718	1800	81000	21	Group 3
7/13/2017	61104 D	242610	15678718	8400	89400	22	Group 3
7/13/2017	61103 D	242610	15678718	6600	96000	23	Group 3
7/13/2017	61102 D	242610	15678718	9600	105600	24	Group 3
7/13/2017	61303 D	242610	15678718	4200	109800	25	Group 3
7/13/2017	61307 D	242610	15678718	12600	122400	26	Group 4
7/13/2017	61301 D	242610	15678718	2400	124800	27	Group 4
7/20/2017	61309 D	242611	15678718	4200	129000	28	Group 4
7/20/2017	61320 D	242611	15678718	1200	130200	29	Group 4
7/20/2017	61317 D	242611	15678718	2400	132600	30	Group 4

Problem: I need to create a schedule  based on the Scheduling Group dynamically. For instance in my DimDate Table - I have a WorkWeekEndingDate.

I need to dynamically schedule each Schedule Group for each WorkWeekEndingDate. 

For Example:

Scheduling Group           Scheduling Week 

Group 1                                1/5/2018

Group 2                                1/12/2018

Group 3                                1/19/2018

ETC                                         ETC

I say "dynamically" - like a measure - so as the report updates the Groups and dates will adjust accordingly. Or as the orders are completed. 

I don't have a working test example yet for this - because the technical logic is escaping me at the moment. 

Again, any assistance is greatly appreciated!

[heathernicole]

To kind of engage in my own question - and maybe spark some ideas...

I've created a WorkWeekScheduler table - based off of the WorkWeekEnding Date...

The idea is to create a "Schedule" Table - then either:

1. set up a parameter that lets you select the Earliest available Schedule week

2. Create measure/ relationship that somehow ties the Group to the appropriate schedule week

3. Create a formula (measure) that determines the next available Schedule week (8 weeks from current week?) And then create another formula (measure) that distributes the Schedule week according to the Group #.

This seems a little simpler in my head... But just to throw some ideas out there

[heathernicole]

@Sean @ImkeF  - as a potential solution... 

do know of a way to create an incrementing date field where you can set the start date? 

I'm trying to create a "Group Key Table" - setting Id 1 - to the first available schedule week (1/5/18). From there incrementing every 6 days per row... I know how to do it in a Date TABLE - but not just as a counter, in essence. 

=[Date]-WEEKDAY([Date])+6

the incrementing number depending on the start date...?

[ImkeF]

Hi @heathernicole,

please check this PQ-approach:

List.Dates(Date.From(Date.EndOfWeek(DateTime.LocalNow())), 100, #duration(7,0,0,0))

The 2nd argument (100) is the lenght of the table, you might need to adjust it to your needs.

Or do you need a DAX-version?

[ImkeF]

The DAX-version would be this:

WeeklyCal =
GENERATESERIES (
    (
        NOW () - WEEKDAY ( NOW () )
            + 6
    ),
    DATE ( YEAR ( NOW () ), 12, 31 ),
    7
)

[heathernicole · Accepted solution]

So this took a bit of finagling - but it's created an Auto-Scheduler. 🙂 It still needs modification - but the core elelements are working. @ImkeF @Sean - THANK YOU for helping me with this!!

Here's the end result: 

First thing I did was sort the table by the Factory Scheduled date and then by the Event Date Actual.

I created a Running Total Column starting with the next row that had NOT been scheduled by the Factory yet. Calculated on the Open QTY. column for each order.

Running Total COLUMN = IF(' Data Domestic'[Factory Scheduled Date] <> BLANK(), 0,
(CALCULATE (
    SUM ( ' Data Domestic'[SO Open QTY.] ),
    ALLEXCEPT ( ' Data Domestic', ' Data Domestic'[Index] ),
    'Data Domestic'[Index] <= EARLIER ( ' Data Domestic'[Index] )
)))

Next,  using @Sean 's formula (modified slightly) the next formula grouped the Running Total Column by about 40,000 pieces - This is called the Scheduling Group Column (basically creates a scheduling ID).

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

Creating an End Week Scheduler (pulling the work week end date (Friday) from the Calendar table - I gave each date an ID (so I could create a relationship to the Scheduling Group Column).

Basically, if the Factory hasn't schedule the item yet - as an estimated time to schedule - the "Auto-Scheduler" will give it a date based on the Scheduling group (determined by the grouping of about 40,000 pieces per week).