Average number of days

Questions & Answers
1

Hi,
We are using the below formula to calculate the difference in duration between a certain schedule and its baseline,

DateDiffWorkdays(
[Issue].CurrentHierarchyMember.get(‘Salesforce Close Date’),
[Issue].CurrentHierarchyMember.get(‘Target Planning Start Date’))

The formula works fine, but I would like calculate the Average number of days

2

Your code would calculate the difference between two dates only at issue level as it is using two properties imported as custom fields.
If you would like to calculate the average result from all issues (for the given report context), you would need to create a new calculated measure that would iterate through issue-level and calculate the average results from these differences.

Try this code:

NonZero(

Avg(

Filter(

Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),

Not IsEmpty(

[Issue].CurrentHierarchyMember.get(‘Salesforce Close Date’)

)

AND

Not IsEmpty(

[Issue].CurrentHierarchyMember.get(‘Target Planning Start Date’)

)

AND

[Measures].[Issues created]>0

),

DateDiffWorkdays(

[Issue].CurrentHierarchyMember.get(‘Salesforce Close Date’),

[Issue].CurrentHierarchyMember.get(‘Target Planning Start Date’))

)

)

1 Like