Calculating days between dates in same column

Hi snortham ,

 

Here I suggest you to add an index column by group of [campaign.id] and [change_date].

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE3sVTSUTIyMDLSNzTUNwKyHZNLMstSdX2AhFKsDlYlnnkKAUX5yanFxdhUmGBTYWqIpMLQBKs9mGowLTJDVmJGWIkFYZuMDAkaYwTytUd+Toquc05mal4JVjXG2AMPRY0FdnOMkdVYYjcHQw0x5mB6C6HESN8UTUUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [compaign.id = _t, change_date = _t, #"previous_value - key" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"compaign.id", Int64.Type}, {"change_date", type date}, {"previous_value - key", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"compaign.id", "campaign.id"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"campaign.id", "change_date"}, {{"Rows", each _, type table [campaign.id=nullable number, change_date=nullable date, #"previous_value - key"=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"previous_value - key", "Index"}, {"Custom.previous_value - key", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom.previous_value - key", "previous_value - key"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Index", Int64.Type}})
in
    #"Changed Type1"

For refernece: Create Row Number for Each Group in Power BI using Power Query

New table looks like as below.

Then update your calculated column code as below.

Days in status =
IF (
    campaigns_logs[Index] = 1,
    [days since]
        - LOOKUPVALUE (
            campaigns_logs[days since],
            campaigns_logs[campaign.id], [campaign.id],
            campaigns_logs[change_date].[Date],
                CALCULATE (
                    MAX ( campaigns_logs[change_date].[Date] ),
                    FILTER (
                        campaigns_logs,
                        campaigns_logs[campaign.id] = EARLIER ( campaigns_logs[campaign.id] )
                            && campaigns_logs[change_date] < EARLIER ( campaigns_logs[change_date] )
                    )
                )
        ),
    0
)

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.