Duration between two date fields

Hello all, we have a jira project where issue type story have several date fields. We want to find average number of workdays between two date fields for all the stories in the project. I have used the below formula to get the number of workdays between two date fields. This works for individual issues but I’m struggling to generate a report of average workdays for all the issues in the project.

DateDiffWorkdays([Measures].[Issue Date of Request],
    [Measures].[Issue Prep complete date])

Any help is appreciated.

I was able to solve this by using following formula:

This helped me to get consolidated count for different dimensions.

Hi @Sarath,

I am happy you found a similar solution on the community forum and adjusted it to your use case.

You can also achieve the average calculation with the Avg() function. The formula could look similar to the one below:

Avg(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    Not IsEmpty([Issue].CurrentMember.Getdate('Date of Request'))
    AND
    Not IsEmpty([Issue].CurrentMember.GetDate('Prep complete date'))
    AND
    [Measures].[Issues with Prep complete date]>0
  ),
  DateDiffWorkdays(
    [Issue].CurrentMember.Getdate('Date of Request'),
    [Issue].CurrentMember.GetDate('Prep complete date')
  )    
)

Best,
Roberts // support@eazybi.com

1 Like

Hi @roberts.cacus, thank you for the formula. While using it, I received following error. Could you help me getting it fixed?

Formula is not valid:
No function matches signature ‘IsEmpty()’

Also, how can I count working day as 1, if the start date and end date are same. We’re defining if start date and end date are same then it is 1. When we datediffworkdays function and if start and end dates are same, then it will show as zero.

So, I tried to work this in following way.
CASE WHEN
SUM(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Issues created],
[Status].[Requirement Fulfilled])>0
),
(DateDiffWorkdays(
[Issue].CurrentMember.Getdate(“start date”),
[Issue].CurrentMember.GetDate(“end date”)
))
)=0
THEN
sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Issues created],
[Status].[Requirement Fulfilled])>0
),
(DateDiffWorkdays(
[Issue].CurrentMember.Getdate(“start date”),
[Issue].CurrentMember.GetDate(“end date”)
)+1)
)
WHEN
SUM(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Issues created],
[Status].[Requirement Fulfilled])>0
),
(DateDiffWorkdays(
[Issue].CurrentMember.Getdate(“start date”),
[Issue].CurrentMember.GetDate(“end date”)
))
)>0
THEN
sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Issues created],
[Status].[Requirement Fulfilled])>0
),
(DateDiffWorkdays(
[Issue].CurrentMember.Getdate(“start date”),
[Issue].CurrentMember.GetDate(“end date”)
))
)
END

This is working for individual issue. But for total count, it won’t consider zero. In the attached image, total value shows 26 instead of 27 and row count is 4. Last value 1, is forced using above formula. If I can count that 1 there, then I’m good.
No data found when drilling through issue - having this issue as well, no data found.

Hi @Sarath ,

Please share the formula where you received the error for IsEmpty(). It is a MDX function and should not cause an error:

https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/isempty

Regarding counting working days as 1 if, the start and end dates are the same, try using the DateAddWorkdays() function. See an example below:

DateDiffWorkDays(
  DateAddWorkDays(DateWithoutTime([Measures].[Issue created date]),0),
  DateAddWorkDays(DateWithoutTime([Measures].[Issue resolution date]),1)
)

https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/dateaddworkdays

Best,
Roberts // support@eazybi.com