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

[Anonymous]

I have a table with the following:

- A column with task numbers. Each task number can have multiple rows as the task flows between different stages. Therefore, there are multiple rows with the same task number.

- A column "row number." This is basically an index row. The more recent the I have found that there are a couple of duplicate index numbers on our table unfortunately, but the this column generally goes in order (ie the later a stage starts, the higher the row number is)

- Each row has a "Start Date" and "End Date"

My user wants me to create the following for each Task Number:

- a column that returns the number of days between the task number's earliest start date and the task number's highest end date. For example, for task 30006591, the earliest Task Start date is 9/30/2011 and the highest Task Finish date is 4/1/2012, so this number would be 184 days. This would only be for columns on completed tasks.

- a column that returns the number of days between today's date and the task number's earliest start date. This would only be for columns that are missing a "Task Finish" date. For example, task number 6989569's earliest start date is 3/1/2012. However task 6989569 is missing an End Date on it's highest row number 39371, so we know this task is incomplete. In this case, we would want to provide the number of dates between today's date and the earliest Start date 3/1/2012. 

Currently we have columns that subtract the current row's end date from the current row's start date. However, since each task has multiple rows, that is not the full picture.

Task Number	Row Number	Task Start	Task Finish	Current Row Aging	Current Row Cycle Time	Goal Column 1: Incomplete Task Aging: Days between today's date and the start date for the earliest row number for this task number	Goal Column 2: Completed Task Cycle Time: Days between Earliest Row Number Task Start Date and Highest Row Number Task End Date
30006591	5228	9/30/2011	2/1/2012	124.00			184
16770363	8945	9/30/2011	4/1/2021	3471.00			4231
30006591	29871	2/1/2012	4/1/2012	60.00			184
6989569	15217	3/1/2012	3/29/2018	2219.00		4122
6989569	20333	3/30/2018	1/14/2019	290.00		4122
6989569	25436	1/16/2019	2/28/2021	774.00		4122
6989569	28846	3/1/2021	12/31/2021	305.00		4122
16770363	34936	4/1/2021	5/1/2023	760.00			4231
6989569	39371	1/1/2022			529.00	4122
34364789	45272	2/4/2022			495.00	495

[MFelix · Accepted solution]

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"

[Anonymous]

This is amazing! Your solution reminds me that mental agility is so, so important when problem-solving. I was so fixated on thinking in terms of "min start date vs. max finish date" that I completely overlooked looking for a "min finish date." I always learn so much from the creative solutions on this forum and I really appreciate your devoting your time to this.