Calculating days between dates in same column

[snortham]

I am trying to calculate the number of days a campaign stayed in each status. With my current formula, if a campaign id has multiple entries on the same day, it counts days in status twice, rather than a 0. I have highlighted a few examples. 

This is the current formula I am using with [days since] being # of days since the campaign was created.

Days in status = [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]))))

 
 

[Smshinde21]

Hi All,

I'm looking for a support.

I want to calculate number of days between first and last date in the same column where I'm having multiple same ID entries with multiple dates

Can I get support with the DAX function logic? 🙏

[v-rzhou-msft · Accepted solution]

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.

[danextian]

Hi @snortham ,

You can use SUMX to virtually remove the duplicates within a measure.

=
SUMX (
    // create a virtual table of unique rows of campaign id, status and days in status
    SUMMARIZE (
        'Table',
        'Table'[Campaign ID],
        'Table'[Status],
        'Table'[Days in status]
    ),
    'Table'[Days in status]
)

=SUMX(VALUES(