Calculate the time difference between selecting and removing an option from a single-select dropdown Custom Field

Questions & Answers
, ,
1

Hi Team,
I need a calculated member formula to calculate the date difference when a single-select custom field option is selected and then removed.
I have a Blocked Type Custom Field with two options: Team A and Team B. I want to calculate the number of days an issue remains in the custom field whether selected with Team A or Team B.

Thanks

2

Hi @Kumar_T

If this is a single-value custom field, you can import the change history for the field: Import issue change history

You just need to add the following parameters to the advanced settings code of the custom field:

separate_table = true
changes = true

Then, perform a double import → Run import without the field selected, and then run the import with the field selected.

After this, you will be able to use a measure like this:

DateDiffDays(
  TimestampToDate(
    (
      [Measures].[Transition to first timestamp],
      [Transition Field].[Blocked Type]
    )
  ),
  TimestampToDate(
    (
      [Measures].[Transition from last timestamp],
      [Transition Field].[Blocked Type]
    )
  )
)

​Best regards,
​Nauris