Lead time

[IF]

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.

Date	Type	Drive	LC
2021-05-10T05:54:03.0630000	D	1	10
2021-05-10T05:55:13.5970000	D	1	13
2021-05-10T05:58:16.5370000	C	1	17
2021-05-10T05:58:22.6100000	D	1	10
2021-05-10T05:58:24.0500000	C	0	13
2021-05-10T06:15:25.6900000	D	1	10
2021-05-10T06:16:26.9870000	C	1	14
2021-05-10T06:16:28.7200000	D	1	15
2021-05-10T06:17:40.6470000	D	1	11
2021-05-10T06:17:47.6630000	B	0	10
2021-05-10T06:18:48.0230000	C	1	15
2021-05-10T06:18:48.3500000	D	1	16
2021-05-10T06:19:55.5700000	C	1	16
2021-05-10T06:19:57.1630000	D	1	17
2021-05-10T06:20:00.0700000	C	0	17
2021-05-10T06:26:00.7100000	D	1	19
2021-05-10T06:26:03.0100000	C	0	17
2021-05-10T06:28:10.5400000	C	0	17
2021-05-10T06:28:15.0570000	C	0	17
2021-05-10T06:28:19.1370000	B	1	11
2021-05-10T06:28:51.0630000	C	1	18
2021-05-10T06:29:54.1870000	C	1	19
2021-05-10T06:29:55.6570000	D	1	20
2021-05-10T06:32:05.7700000	B	1	12
2021-05-10T06:32:07.7430000	D	0	21
2021-05-10T06:33:10.5570000	C	1	20
2021-05-10T06:33:12.2770000	D	1	21
2021-05-10T06:33:19.5730000	C	1	22
2021-05-10T06:34:19.5730000	B	1	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:

https://www.dropbox.com/s/nxkjcc6s6gddvoi/LeadTime.pbix?dl=0

Thanks in advance!

[v-eqin-msft]

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]

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!

[CNENFRNL]

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]

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,