DAX or Power Query Column that finds days between dates on different rows by index

Hi Anonymous ,

I did the following:

• Replaced all the null of dates by 01/01/1900
• Group the column by Task number
  
  • MaxEnd Date = Max Task Finish
  • MinEnd = Min Task Finish
  • MinStart = Min Task Start
• Added custom column:

if [MaxEnd] = #date(1900 , 1, 1) or [MinEnd] = #date(1900,1,1) then Date.From (DateTime.LocalNow()) else null

• Merged querie with itself based on the step before the Replacement of the values
• Expanded the columns of the dates
• Added two columns:

Incomplete = [Custom]-[MinStart]
Completed =if [Custom] = null then [MaxEnd] - [MinStart] else null

• Removed additional columns

If you want a single column use the following code:

if [Custom] <> null then [Custom]-[MinStart] else
 [MaxEnd] - [MinStart]

Complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVFRrsMwCLvK1O/pBQyEcJaq97/GAtFbq2r9iGKBHYyz75sQUbfg7b0ZMOYVTaiBOEtonBATMvSPaILXdrz3jbs7SZdZGKF202npkFDU+Sq8DEQMv03RE3a6ynqMsB5pxMCeD59UaYjEaR/g+C0EiUixl9Fkc2NNXP14mAhT6Yvcv+SG8b+iuz4Ix9D+tVpcRpMzG7KHTEWjRl6CtAWz60/ZSEglyouLoqxjqFjqC+Y66iMFanDUOnoXaJS54/gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task Number" = _t, #"Row Number" = _t, #"Task Start" = _t, #"Task Finish" = _t, #"Current Row Aging" = _t, #"Current Row Cycle Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Number", Int64.Type}, {"Row Number", Int64.Type}, {"Task Start", type text}, {"Task Finish", type text}, {"Current Row Aging", type text}, {"Current Row Cycle Time", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Current Row Cycle Time", type number},{"Current Row Aging", type number}}, "en-US"),
    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Task Start", type date},{"Task Finish", type date}}, "en-US"),
    Custom1 = #"Changed Type with Locale2",
    #"Replaced Value" = Table.ReplaceValue(Custom1,null,#date(1900, 1, 1),Replacer.ReplaceValue,{"Task Finish"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Task Number"}, {{"MaxEnd", each List.Max([Task Finish]), type nullable date}, {"MinEnd", each List.Min([Task Finish]), type nullable date}, {"MinStart", each List.Min([Task Start]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if [MaxEnd] = #date(1900 , 1, 1) or [MinEnd] = #date(1900,1,1) then Date.From (DateTime.LocalNow()) else null),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type with Locale2", {"Task Number"}, #"Added Custom", {"Task Number"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"MaxEnd", "MinStart", "Custom"}, {"MaxEnd", "MinStart", "Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Incomplete Task Aging", each [Custom]-[MinStart]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Completed Task Cycle", each if [Custom] = null then [MaxEnd] - [MinStart] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each if [Custom] <> null then [Custom]-[MinStart] else
 [MaxEnd] - [MinStart]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"MaxEnd", "MinStart", "Custom"})
in
    #"Removed Columns"