Power BI date difference between rows in a single column with conditions

Question

I have a table with the columns: Account, Current stage, Old Stage, New Stage, Change date (see example below). I need to find the datediff for each stage length on accounts. Dax or Power Query, whichever is easier. Is it even possible to get the results seen in the sample below?

Account   Current Stage   Old Stage    New Stage    Change Date    Stage Length(days)
ABC       Testing         Demo         Testing      1/1/2022           31
ABC       Testing         Testing                   2/1/2022    
XYZ       Completed       Demo         Testing      1/1/2022           31
XYZ       Completed       Testing      Completed    2/1/2022           59
XYZ       Completed       Completed                 4/1/2022

user_13366191 · Answer

To get the previous change date, in Power Query, merge the table with itself on [Account] and [Old Stage] equal [Account] and [New Stage]. From the merge, extract the [Change Date].

You can then use either Power Query or DAX to calculate the difference between the two dates. Here is how you can use the Duration.Days function in Power Query:

= Table.AddColumn(#"Expanded Changed Type1", "DateDiff", each Duration.Days([Change Date]-[Previous Change Date]))

Here is the full M code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCkktLsnMSweyXFJz81EEDPUN9Y0MjIyUYnUwVSNYQGSErDIiMgoo5JyfW5CTWpKaQtBkTPUIhciiBCxBZgORCVx1LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"Current Stage" = _t, #"Old Stage" = _t, #"New Stage" = _t, #"Change Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Current Stage", type text}, {"Old Stage", type text}, {"New Stage", type text}, {"Change Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account", "Old Stage"}, #"Changed Type", {"Account", "New Stage"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Change Date"}, {"Previous Change Date"}),
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type1", "DateDiff", each Duration.Days([Change Date]-[Previous Change Date]))
in
    #"Added Custom"

user_2872922 · Answer

In Power Query, you can

Group by Account
Add a column to each sub-table that computes the day difference from one line to the next using the List.Generate function
Re-expand the table

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],

    #"Grouped Rows" = Table.Group(Source, {"Account"}, {
        {"With Stage Length", (t)=>
            Table.FromColumns(
                Table.ToColumns(t) & {
                    List.Generate(
                        ()=>[d=try Duration.Days(t[Change Date]{1} - t[Change Date]{0}) otherwise null, idx = 0],
                        each [idx] < Table.RowCount(t),
                        each [d=try Duration.Days(t[Change Date]{[idx]+2} - t[Change Date]{[idx]+1}) otherwise null, idx = [idx]+1],
                        each [d])},
                            {"Account","Current Stage","Old Stage","New Stage","Change Date","Stage Length (days)"})}       
        }),
    #"Expanded With Stage Length" = Table.ExpandTableColumn(#"Grouped Rows", "With Stage Length", {"Current Stage", "Old Stage", "New Stage", "Change Date", "Stage Length (days)"}, {"Current Stage", "Old Stage", "New Stage", "Change Date", "Stage Length (days)"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded With Stage Length",{{"Account", type text},{"Current Stage", type text}, {"Old Stage", type text}, {"New Stage", type text}, {"Change Date", type date}, {"Stage Length (days)", Int64.Type}})
in
    #"Changed Type"

Power BI date difference between rows in a single column with conditions

2 Answers