Struggling with cycle time calculation

[PBI_37]

Hi everyone,

I have a data set from which I want to calculate the production cycle time of each step :

• How to calculate the cycle time of each step ?

For semi finished goods it is the variance with the previous step.

But for finished goods it is the variance with the last step of manufacturing.

I don't know if I need the calculations/transformations in Power Query or in DAX.

• Desired outpout :

I would like to create this visual for each finished good :

Here the link pf PBI file :

https://drive.google.com/file/d/1BZMPi3Rx7hpEt9-hg_73gobNJ44NRt8f/view?usp=sharing

Thank you for your help

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosSc4wVNJRCnYDkSDsm5hXmgbmGCjF6qAoMQKSRjAlIIahKboSYyBpDFMCYhihKXFzB5EmQByQmJydmJ6Zlw5kmxqgqzLCosoYySwjHI7GUILhaGMDVCVu7iZE2OXmbopFlRmGWWZYVJkAVcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batch ID" = _t, Item = _t, #"Step ID" = _t, #"Manuf Step" = _t, #"Cumulated Cycle Time" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Batch ID", type text}, {"Item", type text}, {"Manuf Step", type text}, {"Cumulated Cycle Time", Int64.Type}}),
    Transform = (tbl) =>
        let
            #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type)
        in
        Table.AddColumn(#"Added Index", "Step Time", each if [Index]=0 then [Cumulated Cycle Time] else [Cumulated Cycle Time]-#"Added Index"[Cumulated Cycle Time]{
                if Text.StartsWith([Item],"SF") then [Index]-1 else List.Last(Table.SelectRows(#"Added Index",each Text.StartsWith([Item],"SF"))[Index])}),
    #"Grouped Rows" = Table.Group(#"Type modifié", {"Batch ID"}, {{"Rows", each _, type table [Batch ID=nullable text, Item=nullable text, Step ID=nullable text, Manuf Step=nullable text, Cumulated Cycle Time=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Transform([Rows])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Item", "Step ID", "Manuf Step", "Cumulated Cycle Time", "Step Time"}, {"Item", "Step ID", "Manuf Step", "Cumulated Cycle Time", "Step Time"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

here is the part that does the work

  Transform = (tbl) =>
    let
      #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type)
    in
      Table.AddColumn(
        #"Added Index", 
        "Step Time", 
        each 
          if [Index] = 0 then
            [Cumulated Cycle Time]
          else
            [Cumulated Cycle Time]
              - #"Added Index"
                [Cumulated Cycle Time]
                {
                  
                    if Text.StartsWith([Item], "SF") then
                      [Index] - 1
                    else
                      List.Last(
                        Table.SelectRows(#"Added Index", each Text.StartsWith([Item], "SF"))[Index]
                      )
                }
      )

[PBI_37]

Thank you very much @lbendlin. It is perfect.

But I was not able to build this visual : One row per Finished Good with the split of cycle time per step

[lbendlin]

yeah, that's too far off .  How can you guarantee that Manuf will always only go from 1 to 3?  What if you have Manuf4 ?

[PBI_37]

The manufacturing steps are always the same. 2 or 3 depending on the Finished Goods.