Forum Discussion

ToddMate's avatar
ToddMate
Helper II
7 years ago
Solved

DateDiff where End Date is Null

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)


 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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's avatar
      ToddMate
      Helper II
      7 years ago

      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 ?