DateDiff where End Date is Null

[ToddMate]

Hi All,

I need assistance, with dates and tenures .. but not the usual A Date - B Date scenario as i have a measure to do this. What i am looking for is an extension to this measure where, if the end date is null treat the end date as today and perform the End Date - Start Date calculation. .

The scenario is i have a Connected Date and a Disconnected Date, where the Disconnected Date is null minus the Connection Date from Todays Date and return a whole number.

I am using two measure which nicely calculate the days between two populated dates. The measure is:

Tenure = 1.0*(X_Churns[Churn Date]-X_Connects[Connect Date])

Can anyone assist with the modification to this or an alternate measure to derive the outcome mentioned.

My tables are: (Expected reult for the yellow field would be 43 (based on 26/03/2019 > 08/05/2019)

[Mariusz · Accepted solution]

Hi @ToddMate ,

You can use the code below.

Tenure =  1.0 * ( IF(X_Churns[Churn Date] = BLANK(), TODAY(), X_Churns[Churn Date]) - X_Connects[Connect Date] )

Hope it helps.

Mariusz

[ToddMate]

Hi @Mariusz ,

I was wondering if you could help out on an extension of this measure.

The table above shows the Connect Date and the Churn Date and then calculates the Tenure which works great.

How would you determine the average tenure based on grouping of the account number column ?

[ToddMate]

@Mariusz  Hey thankyou so much! This works perfectly!