Days between Start Date and previous record End Date

[joyhackett]

Hi,

I have a contract model much like this simplified example:

dim_user

user_id

1

fact_user_contract_history

user_id	type	start	end	previous status	current status
1	A	5/1/2021	5/15/2021	inactive	active
1	B	5/15/2021	5/31/2021	active	active
1	B	5/31/2021		active	inactive
1	C	2/18/2022		inactive	active

Filters:

• date range (dim_date[full_date])
• type (dim_type[type])

For the contracts that have current status = active, I would like an "Activation Type" measure on each row that says if the row is the user's first contract ("New"), renewal contract (no gap in time = "Renew"), or a rejoin (a gap in time = "Rejoin").

user_id	user_index	type	start	end	previous status	current status	Activation Type
1	1	A	5/1/2021	5/15/2021	inactive	active	NEW
1	2	B	5/15/2021	5/31/2021	active	active	RENEW
1	3	B	5/31/2021		active	inactive
1	4	C	2/18/2022		inactive	active	REJOIN

Unfortunately, the easy way of making this a calculated column is not possible in my case because when a type filter is applied, the Activation Type may change. Example (type = C only)

user_id	user_index	type	start	end	previous status	current status	Activation Type
1	4	C	2/18/2022		inactive	active	NEW

I have a measure that counts "NEW", but haven't figured out how to create an "Activation Type" which I can use to get the counts.

Thanks in advance for any guidance!!

First Active Date =

CALCULATE(

MIN(fact_user_contract_history[start date]), ALLEXCEPT( dim_user,dim_user_current[user_id]),

FILTER(fact_user_contract_history, fact_user_contract_history[current status] = "Active")

)

Count of New =

CALCULATE(

COUNTROWS(dim_user),

FILTER(dim_user, [First Active Contract Date] >= MIN(dim_date[full_date])
&& [First Active Membership Date] <= MAX(dim_date[full_date]))

)

[joyhackett]

Thanks, that wasn't it, but it gave me an idea.

I added a column active_index to the fact table, so the contracts are indexed within each user group. This allows me to create a measure:

DaysBetweenActive=

//get active_index for current record
VAR IndexNum = CALCULATE(

MAX(fact_user_contract_history[active_index] ))

//get end date of previous record 

VAR PreviousActiveDate = CALCULATE(

MAX(fact_user_contract_history[end]),

FILTER ( ALLSELECTED(fact_user_contract_history), fact_user_contract_history[active_index] = IndexNum - 1)

//calculate days between current record start date and previous record end date

RETURN

DATEDIFF(_PreviousActiveEndDate,MIN(fact_user_contract_history[start]),DAY)

The results are correct. However, I'm still unable to assign an Activation Type value:

I tried a SWITCH measure, but when I add that to the table, the records duplicate over and over...

[v-jingzhang]

Hi @joyhackett 

How did you write the SWITCH measure? Maybe you can try this one

Activation Type =
SWITCH (
    TRUE (),
    [DaysBetweenActive] < 0, "NEW",
    [DaysBetweenActive] = 0, "RENEW",
    "REJOIN"
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@joyhackett , try a measure like

Measure =
VAR __id = MAX ('Table'[user_id] )
VAR __date = CALCULATE ( Min('Table'[start] ), ALLSELECTED ('Table' ), 'Table'[user_id] = __id )
return
CALCULATE ( Countrows ('Table' ), VALUES ('Table'[user_id] ),'Table'[user_id] = __id,'Table'[start] = __date )