Lead Time when dates in different rows and columns

[StillLearningMe]

I've been struggling for days to figure this out so I'm hoping someone here can help me with a fresh set of eyes.

I've got a table where one date is on a different row and a different column to another date. However I want to show the lead time between those 2 dates.

I have it similar to this:

ID	Index	1st date	2nd date	3rd date
1	1	Null	Null	28/4/2022
2	1	Null	3/4/2022	Null
3	1	1/1/2022	Null	Null
4	2	Null	Null	2/2/2022
5	2	Null	29/12/2021	Null
6	2	20/12/2021	Null	Null

I want to be able to show the lead time between "1st date" and "2nd date" and also on the same graph be able to show lead time between "2nd date" and "3rd date".

[VahidDM · Accepted solution]

Hi @StillLearningMe 

One way is to summarize your table, use the following DAX code to create a new table:

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Index],
    "1st date", MIN ( 'Table'[1st date] ),
    "2nd date", MIN ( 'Table'[2nd date] ),
    "3rd date", MIN ( 'Table'[3rd date] )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Index", Int64.Type}, {"1st date", type date}, {"2nd date", type date}, {"3rd date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Hope this helps.

[StillLearningMe]

Yes, thank you. That helped perfectly.