Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Aggregate in numerical order

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!

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Anonymous, 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

     

13 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

     

    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"

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        dufoq3
        Community Champion
        2 years ago

        Hi Anonymous, 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