How to find the difference between 2 custom date fields

Hi all,

How do I find the difference between 2 custom date fields from a Jira ticket? Then how to display the average of the difference? Ideally this will be a bar graph showing a change in average between quarters.

To address the first question I tried the below:
DateDiffDays([Measures].[Issue with Onboarding Kickoff], [Measures].[Issue with Estimated Live Date])

*The 2 measures are being added in the custom tab as data type “date”

Hi @sfoster,

Welcome to the eazyBI community :tada: !

The DateDiffDays() function is the right fit for the job. My only concern is the name of the measures you use. Usually, eazyBI imports measures “Issues with ” as numeric measures tied to the Time dimension. The particular dates for each issue can be found under “Issue properties” in Measures. See more details here on how particular fields can be imported into eazyBI - Jira custom fields.

For example, to calculate the difference between the dates “Target start” and “Target end”, the formula would look similar to the one below:

DateDiffDays(
  [Measures].[Issue target start],
  [Measures].[Issue target end]
)

For the second part, to calculate the average, determine which date should fall in the quarters you want to compare. Is it the first or second date? In my example, I will consider the second date. The formula for average duration could look similar to the one below:

Avg(
  Filter(
    -- iterate through issues
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- issues have Target start date
    Not IsEmpty([Issue].CurrentMember.Get('Target start'))
  ),
  -- target end date is in the current Time dimension period
  CASE WHEN [Measures].[Issues with Target end] > 0
  THEN
  -- calculate the difference
  DateDiffDays(
    [Measures].[Issue target start],
    [Measures].[Issue target end]
  )
  END
)

Please see more examples about the Avg() function here - Avg.

Best,
Roberts // support@eazybi.com