Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
powerquest1234
Helper III
Helper III

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

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 NumberRow NumberTask StartTask FinishCurrent Row AgingCurrent 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 

3000659152289/30/20112/1/2012124.00  184
1677036389459/30/20114/1/20213471.00  4231
30006591298712/1/20124/1/201260.00  184
6989569152173/1/20123/29/20182219.00 4122 
6989569203333/30/20181/14/2019290.00 4122 
6989569254361/16/20192/28/2021774.00 4122 
6989569288463/1/202112/31/2021305.00 4122 
16770363349364/1/20215/1/2023760.00  4231
6989569393711/1/2022  529.004122 
34364789452722/4/2022  495.00495 

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @powerquest1234 ,

 

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

MFelix_0-1686778392595.png

 

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @powerquest1234 ,

 

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

MFelix_0-1686778392595.png

 

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.