cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IF
Post Partisan
Post Partisan

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:

LT ex.jpg

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!
4 REPLIES 4
Eyelyn9
Community Support
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

IF
Post Partisan
Post Partisan

Hi,

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

112.jpg

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:

113.jpg

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
Super User III
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"}),
    #"Added Custom" = Table.AddColumn(#"Expanded grp", "Seconds", each Duration.TotalSeconds([End]-[Start]))
in
    #"Added Custom"

 

Screenshot 2021-05-19 005909.png

IF
Post Partisan
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,

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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