Aggregate in numerical order

[kamirane]

Hi - I will be glad if you can help me to solve following:

My current table consist of manufacturing Order which includes 3 operations (sometimes more) and a final plan date for the last operation (operation 3 packing). Each operation has a lead time but I don't have any start date for each operation. 

My idea is to aggregate the lead times in Operation numerical order and then create start date based on the aggregated lead time.

Basically I need two new columns as shown below:

where lead time for Operation 1 = 2 + 7 + 1

Lead time for Operation 2 = 7 + 1

and lead time for last Operation 3 will be = 1 (since there are no next operation)

The start date for Operations = Plan date - Aggregate Lead time

Is it possible to solve my issue in DAX?  

My data in my above table is coming from two different fact tables, I've merged two fact tables. 

 Thanks!

[ronrsnfld]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDIwUNJRMjQwMDYB0UDsnJOamAekjYDYWN/IQt/IwMhEKVYHi3KQEuf8xBIgZU5YtTEQByQmZ0PtQVIdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, Product = _t, Operation = _t, #"Operation name" = _t, #"Operation Leadtime" = _t, #"Order Plan date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"Order #", Int64.Type},{"Product", Int64.Type}, 
            {"Operation", Int64.Type}, {"Operation name", type text}, 
            {"Operation Leadtime", Int64.Type}, {"Order Plan date", type date}
            }),

//Assuming you will have more than one order number, process each ordder number separately
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order #"}, {
        {"Starts", (t)=>
            let 
                //ensure operations in proper order
                sorted = Table.Sort(t, {"Operation", Order.Ascending}),

                //compute aggregate lead times
                aggLeadTime = List.Accumulate(
                    {0..Table.RowCount(sorted)-1},
                    {},
                    (s,c)=> s & {List.Sum(List.RemoveFirstN(sorted[Operation Leadtime],c))}),

                //compute start dates
                startDt = List.Accumulate(
                    aggLeadTime,
                    {},
                    (s,c)=> s & {Date.AddDays(sorted[Order Plan date]{0},-c)}),

                addColumns = Table.FromColumns(
                    Table.ToColumns(sorted)
                    & {aggLeadTime}
                    & {startDt},
                    Table.ColumnNames(sorted) & {"Aggregate Lead time"} & {"Start date"})
            in 
               addColumns, 
               type table[#"Order #"=Int64.Type, Product=Int64.Type, Operation=Int64.Type, Operation name=text, Operation Leadtime=Int64.Type, Order Plan date=date, Aggregate Lead time=Int64.Type, Start date = date]
        }}),
    #"Expanded Starts" = Table.ExpandTableColumn(#"Grouped Rows", "Starts", {"Product", "Operation", "Operation name", "Operation Leadtime", "Order Plan date", "Aggregate Lead time", "Start date"})
    
in
    #"Expanded Starts"

[kamirane]

Thank you so much for your reply. 

I realize that I perhaps should posted this in Desktop category and not in PowerQuerry because I'm not that advanzed in coding. Is it possible to solve my issue in DAX?  

I also forgot to mention that data in my above table is coming from two different fact tables. I've merged two fact tables.

[dufoq3 · Accepted solution]

Hi @kamirane, why not to use Power Query solution? If you don't know how to use this query - read note at the botom of my post.

I've added 3 more rows to your sample data just to demonstrate that we consider every Order # separately.

EDIT: 2024-03-28 15:07 GMT+1: I've edited my code, and now it is realy fast also with big data

Result

let
    fn_AggLT = 
        (lst as list)=>
        List.Reverse(List.Generate(
            ()=> [ x = List.Count(lst)-1, y = lst{x} ],
            each [x] >= 0,
            each [ x = [x]-1, y = [y] + lst{x} ],
            each [y] )
        ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDIwUNJRMjQwMDYB0UDsnJOamAekjYDYWN/IQt/IwMhEKVYHi3KQEuf8xBIgZU5YtTEQByQmZ0PtwaLaEIdTjAkrR3KKBWHVSE4xQVUdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, Product = _t, Operation = _t, #"Operation name" = _t, #"Operation Leadtime" = _t, #"Order Plan date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Product", Int64.Type}, {"Operation", Int64.Type}, {"Operation name", type text}, {"Operation Leadtime", type number}, {"Order Plan date", type date}}, "en-US"),
    // Added Aggregated Leadtime into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"Order #"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Operation Leadtime]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_StartDate = Table.AddColumn(CombinedAll, "Start Date", each Date.AddDays([Order Plan date], -[Aggregated Leadtime]), type date)
in
    Ad_StartDate

[kamirane]

Hi,

Thanks allot. Is it possible for you to attach the sample as pbix file? It will be easier for me to solve it 🙂

[dufoq3]

It is, but it is not necessary 🙂 - Read note below my posts - there is a picture with explanation how to use my query. Don't forget to rename your columns - you provided different column names in sample data vs screenshot in next post.

[kamirane]

well you made my day - thanks allot and happy eastern 🙂

[dufoq3]

So have you made it? You're welcome.

[kamirane]

@dufoq3 

Thanks - It's working but not 100% and it's because I don't know how to use the script correctly yet.

Since Order number and plan date is in another table it will be tricky to use them. But it should be possible to aggregate the lead time based on product number and operation number, I will create "Start date" in DAX later.

Here's how I've modified your script (and yes I don't know how to exclude start date 🙂 

The issue now is that it aggregate all lead time based on operation number, it doesn't take product number in account

let
fn_AggLT =
(lst as list)=>
List.Reverse(List.Generate(
()=> [ x = List.Count(lst)-1, y = lst{x} ],
each [x] >= 0,
each [ x = [x]-1, y = [y] + lst{x} ],
each [y] )
),
Source = Sql.Database(Server, Database, [Query="SELECT
DimFacility_Key,
DimWorkCenter_Key,
DimItem_Key,
Facility,
[Product number],
[Product structure type],
[Operation number],
[Sequence number],
substring([Work center],1,3) as [Work Center],
[Operation description],
[Alternate operation],
[Operation elements exists],
[Fixed time],
[Run time],
[Price and time quantity],
[Planned number of workers - setup],
[Planned number of workers - run time],
[Planned number of machine],
[Scrap percentage],
[Cumalative scrap percentage],
[Lead time offset],
[Production days]

FROM [MData].[FactOperations] WHERE [Product structure type] = 'PRO' AND Facility = '300' "]),

ChangedType = Table.TransformColumnTypes(Source,{{"DimFacility_Key", type text},
{"Facility", Int64.Type},
{"Product structure type", type text},
{"Sequence number", Int64.Type},
{"Work Center", Int64.Type},
{"Operation elements exists", Int64.Type},
{"Fixed time", Int64.Type},
{"Run time", Int64.Type},
{"Price and time quantity", Int64.Type},
{"Planned number of workers - setup", Int64.Type},
{"Planned number of workers - run time", Int64.Type},
{"Planned number of machine", Int64.Type},
{"Cumalative scrap percentage", Int64.Type},
{"Scrap percentage", Int64.Type},
{"Alternate operation", Int64.Type},
{"Production days", Int64.Type},
{"Product number", type number},
{"Operation number", Int64.Type},
{"Operation description", type text},
{"Lead time offset", type number}}, "en-US"),
// Added Aggregated Leadtime into inner [All] table
GroupedRows = Table.Group(ChangedType, {"Operation number"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Lead time offset]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
Ad_StartDate = Table.AddColumn(CombinedAll, "Start Date", each [Aggregated Leadtime])
in
Ad_StartDate

This is how my original table looks like when I've merged with another table to get Order number and Planning date in the same table: 

[dufoq3]

Hi, I don't see [Operation Leadtime] in your table which is necessary for this purpose. Could you provide maybe 10-15 rows of your real data (whole table) either as text or upload it to google drive for example. Replace sensitive data with some dummy values.

[kamirane]

Thanks.
Operation leadtime is "lead time offset".
Here's my tabel with dummy numbers: (I tried several time to insert this as table but keep getting error message about invalid HTML) 

DimFacility_Key DimWorkCenter_Key DimItem_Key Facility Product number Product structure type Operation number Sequence number Work Center Operation description Alternate operation Operation elements exists Fixed time Run time Price and time quantity Planned number of workers - setup Planned number of workers - run time Planned number of machine Scrap percentage Cumalative scrap percentage Lead time offset Production days
6|300 6|18000 6|10030555 300 10030555 PRO 20 9999 180 Clean 0 1 0 1 5 0 1 1 0 19 6,72 0
6|300 6|18000 6|10030555 300 10030555 PRO 40 9999 180 Coat 0 0 0 1 5 0 1 1 0 19 5,46 0
6|300 6|38100 6|10030555 300 10030555 PRO 60 9999 381 Test 0 1 0 1 100 0 1 1 0 19 0,42 0
6|300 6|02600 6|10030555 300 10030555 PRO 80 9999 26 Test2 0 0 1 3 1 0 1 1 0 19 2,29 0
6|300 6|38100 6|10030555 300 10030555 PRO 100 9999 381 Packing 0 0 0 1 100 0 1 1 0 19 1,9 0
6|300 6|17600 6|10036666 300 10036666 PRO 20 9999 176 Clean 0 1 0 1 4 0 1 1 0 2 2 0
6|300 6|90200 6|10036666 300 10036666 PRO 60 9999 902 Test 0 1 1 1 1 0 1 1 0 2 0,4 0
6|300 6|02500 6|10036666 300 10036666 PRO 80 9999 25 Test2 0 1 1 1 1 0 1 1 0 2 0,1 0
6|300 6|38100 6|10037777 300 10037777 PRO 20 9999 381 Clean 0 1 0 1 10 0 1 1 0 15 2,6 0
6|300 6|03100 6|10037777 300 10037777 PRO 40 9999 31 Coat 0 0 0 1 5 0 1 1 0 15 0,7 0

[dufoq3]

Hi, in future upload your data to google drive for example, because I had to make some transformations to make it usable 😉

I'm missing any date column in this new sample, so I'm not able to calculate [Start Date] as you requested in 1st post.

You can find added [Aggregated Leadtime] in this query. You have to add date column and then add calculated column:

each Date.AddDays(Date.From([Your_Date_Column]), -Int64.From([Aggregated Leadtime]))

One more importand note: Data is grouped by [Sequence Number] column. If you want to group by different column, change it in GroupedRows step.

let
    fn_AggLT = 
        (lst as list)=>
        List.Reverse(List.Generate(
            ()=> [ x = List.Count(lst)-1, y = lst{x} ],
            each [x] >= 0,
            each [ x = [x]-1, y = [y] + lst{x} ],
            each [y] )
        ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZRNDsIgEIXv0jWLYSjQrj2AxrhrXDTGGKPRhS49vFNAM6WjopKUn4EpXx4Puq5yNwNQKWp1A48egAFrLQ3iJAsslnOqcYi2VIbJZhjMjtv+RG1YzlrL+s/4kOaUxzBeq18p6inFub+mTaAEwqraCRCm0cUQjkFQHtWr7eVaTZWIvxQxQNWSFoCuGKNhGOgSBWY6mByJQ6DC9k8t4sqRGIt+c9ifdsKhvNFDK4lEey6HozIiSQHBoN69MWgtQGD6coIWsJiA+4Lypr7Q2aY5AJlC9IQtRhh5wmaeKEPQHx3hqYwQUmB6DtEQr85Bi26wwZjSHQVTTsEfChMgvngnQlD5iLC+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DimFacility_Key = _t, DimWorkCenter_Key = _t, DimItem_Key = _t, Facility = _t, #"Product number" = _t, #"Product structure type" = _t, #"Operation number" = _t, #"Sequence number" = _t, WorkCenter = _t, #"Operation description" = _t, #"Alternate operation" = _t, #"Operation elements exists" = _t, #"Fixed time" = _t, #"Run time" = _t, #"Price and time quantity" = _t, #"Planned number of workers - setup" = _t, #"Planned number of workers - run time" = _t, #"Planned number of machine" = _t, #"Scrap percentage" = _t, #"Cumalative scrap percentage" = _t, #"Lead time offset" = _t, #"Production days" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Lead time offset", type number}}),
    // Added Aggregated Leadtime into inner [All] table
    GroupedRows = Table.Group(ChangedType, {"Sequence number"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Lead time offset]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[kamirane]

@dufoq3 

Thank you soooo much. It's fixed now. I have both aggregated leadtime and start date (did it in DAX):

 I only had to do one minor change;

Table.Group(ChangedType, {"Sequence number"},

to

Table.Group(ChangedType, {"Product number"},

[dufoq3]

That's great 😉 You're welcome.