cancel
Showing results for
Did you mean:
Post Partisan

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:

SUMMARIZE (
"Drive_1", CALCULATE (
),
"Drive_0", CALCULATE (
))
And to calculate the difference in seconds:

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:

4 REPLIES 4
Community Support

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

Post Partisan

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:

Thank you very much!

Super User III

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"}),
in

Post Partisan

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,

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors