Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago

Lead time

Hi,

 

I could not calculate the lead time in seconds for a specific situation.

 

I have the following example data. I want to calculate the lead time based on the "LC". There are three types B, C,D, and I want to calculate the lead time when the "Drive" is 0 and it changes to 1 for the same Type.

 

DateTypeDriveLC
2021-05-10T05:54:03.0630000D110
2021-05-10T05:55:13.5970000D113
2021-05-10T05:58:16.5370000C117
2021-05-10T05:58:22.6100000D110
2021-05-10T05:58:24.0500000C013
2021-05-10T06:15:25.6900000D110
2021-05-10T06:16:26.9870000C114
2021-05-10T06:16:28.7200000D115
2021-05-10T06:17:40.6470000D111
2021-05-10T06:17:47.6630000B010
2021-05-10T06:18:48.0230000C115
2021-05-10T06:18:48.3500000D116
2021-05-10T06:19:55.5700000C116
2021-05-10T06:19:57.1630000D117
2021-05-10T06:20:00.0700000C017
2021-05-10T06:26:00.7100000D119
2021-05-10T06:26:03.0100000C017
2021-05-10T06:28:10.5400000C017
2021-05-10T06:28:15.0570000C017
2021-05-10T06:28:19.1370000B111
2021-05-10T06:28:51.0630000C118
2021-05-10T06:29:54.1870000C119
2021-05-10T06:29:55.6570000D120
2021-05-10T06:32:05.7700000B112
2021-05-10T06:32:07.7430000D021
2021-05-10T06:33:10.5570000C120
2021-05-10T06:33:12.2770000D121
2021-05-10T06:33:19.5730000C122
2021-05-10T06:34:19.5730000B1

13

 

The lead time should be like this:

Each different colour is for calculation of a lead time. When the same type changes from drive=0 to drive=1, it should count the lead time in seconds.

 

I tried to do with by making a new table:

LeadTime_New =
SUMMARIZE (
'LeadTime',
'LeadTime'[LC],
"Drive_1", CALCULATE (
MIN ( 'LeadTime'[Date] ),
FILTER ( 'LeadTime', 'LeadTime'[Drive] = 1 )
),
"Drive_0", CALCULATE (
MAX ( 'LeadTime'[Date] ),
FILTER ( 'LeadTime', 'LeadTime'[Drive] = 0 )
))
And to calculate the difference in seconds:
Column = DATEDIFF(LeadTime_New[Drive_1],LeadTime_New[Drive_0],SECOND)
 
I want to show this in a matrix or graph. The lead time should be calculated for each time. Therefore I provided a matrix that is highlighted in red rectangle. 
 
I provide the power bi file as well:
 
Thanks in advance!
Need Help

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Power Query solution, enough complicated; thus, I wonder DAX solutions due to complexity.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNLDsMgDESvUrFOJGMwHy/b3iLK/a9RR4XUaUzTBSueZsZjWBaHgH4Gmj3cgJgiQ3CTe8rx2wG3TieI2B+gYEGFfZLLR4eyDSFe2gkUmxLYdok9MdJvJYES4yFTHEBFK5EFZY6gIT+Aslzee3AzU+FYdCbTrkG7XbKgKpvRSiMoa6XzWmR+YADduAmlN7Qr1QEULpXkqVzbCUT/QLU1PlyLQOR1T8WCpKeoIXO61nivAK0FB2QgnQkHUF/LNh1awUP49DS2E0h/qaFS1UpmpvhVpvy79QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Drive = _t, LC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Type", type text}, {"Drive", Int64.Type}, {"LC", Int64.Type}}),
    #"Grouped by Type" = Table.Group(#"Changed Type", {"Type"}, {"grp", each _}),
    #"Grouped by Time" = Table.TransformColumns(
        #"Grouped by Type", 
        {"grp",
        each let t=Table.Group(_, "Drive", {"gp", each _{0}}, 0, (x,y) => Number.From(x<>y)), rs=Table.RemoveFirstN(t, Number.From(t[Drive]{0}=1))
            in rs}
    ),
    #"Extracted Time" = Table.TransformColumns(#"Grouped by Time", {"grp", each Table.FromRows(List.Transform(List.Split(List.Transform([gp], Record.ToList),2), each List.Zip(_){0}), {"Start", "End"})}),
    #"Expanded grp" = Table.ExpandTableColumn(#"Extracted Time", "grp", {"Start", "End"}, {"Start", "End"}),
    #"Added Custom" = Table.AddColumn(#"Expanded grp", "Seconds", each Duration.TotalSeconds([End]-[Start]))
in
    #"Added Custom"

     

    • IF's avatar
      IF
      Post Prodigy
      5 years ago

      Hi! thanks for the quick support. Actually, the result is true with this data. One question, it doesn't work if there are values other than 0 or 1 in column "Drive". I have some other values. I just tried 7 and gave an error. How can I use it if I have values such as 7,-12, 23,13,16, (actually I see more values). Therefore I would like to ignore the others and calculate only the time when it changes from 0 to 1. I can not avoid not having this data.

      Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi IF ,

     

    It seems that the sample you provided is different from the actual data , so please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data to help us clarify your scenario.

     

    Best Regards,
    Eyelyn Qin

    • IF's avatar
      IF
      Post Prodigy
      5 years ago

      Hi,

      Thanks for your support in advance! Actually, my ultimate goal and the original message are based on this scenario:

      I don't know if this can be done.

       

      If it is too complicated, this can be helpful as well but prefer the first one definitely:

      The report has a direct connection to the server. the report that I explained is available at:

      https://www.dropbox.com/s/mmujfqqi4h0p0a5/LeadTime%20v1.pbix?dl=0

      Thank you very much!