AVG Calculation

Questions & Answers
1

Hi All,

Here is the definition I have

Avg(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Customfield_One ])>0),
CASE WHEN
DateCompare([Measures].[Date_One],DateParse(‘Apr 30, 2021’))>0
AND
DateInPeriod([Measures].[Date_Two],[Time])
AND
IsEmpty([Measures].[Date_Three])
THEN
DateDiffWorkdays([Measures].[Date_Two],DateParse(‘today’))

WHEN
DateCompare([Measures].[Date_One],DateParse(‘Apr 30, 2021’))>0
AND
DateInPeriod([Measures].[Date_Two],[Time])
AND
NOT IsEmpty([Measures].[Date_Three])
THEN
DateDiffWorkdays([Measures].[Date_Two],[Measures].[Date_Three])

End)

I am able to get the avgs, but there are issues that are closed/completed and the avg is still counting.
I am thinking of adding another date field called “Date_Four” and automatically updating the date when the issues closed/completed, so the avg can be calculated accordingly.
Now, how can I include the “Date_Four” in my calculation/definition?

Please Help.

2

Hi,

The most straightforward solution for filtering unresolved issues is to check the resolution in the filter condition:

Avg(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measrures].[Issue resolution]="(unresolved)" AND [Measures].[Customfield_One ])>0),
CASE WHEN
DateCompare([Measures].[Date_One],DateParse(‘Apr 30, 2021’))>0
AND
DateInPeriod([Measures].[Date_Two],[Time])
AND
IsEmpty([Measures].[Date_Three])
THEN
DateDiffWorkdays([Measures].[Date_Two],DateParse(‘today’))

WHEN
DateCompare([Measures].[Date_One],DateParse(‘Apr 30, 2021’))>0
AND
DateInPeriod([Measures].[Date_Two],[Time])
AND
NOT IsEmpty([Measures].[Date_Three])
THEN
DateDiffWorkdays([Measures].[Date_Two],[Measures].[Date_Three])

End)

Kindly,
Janis, eazyBI support