Filter issues where a date is in the current month

Questions & Answers
1

Hello, my requirement is to calculate the average between two dates of issues that have prep complete date falling under the current month.

Example:
If there are three issues that have a date field called “Prep Complete date” which are October 10, 2021 ; October 11, 2021 ; October 12, 2021 then I want to calculate the average between the dates “Start Date” & “Complete Date” fields.

I was using this below formula which is calculating the average between two dates. I’m not sure how to include the above condition.

Avg(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    ([Measures].[Issues created],
    [Status].[done])>0
  ),
  (DateDiffWorkdays(
    [Issue].CurrentMember.Getdate("Start Date"),
    [Issue].CurrentMember.GetDate("Complete Date")
  ))    
)
2

Hi @Sarath,

Even if you select Time in your report, you are filtering done issues with created date in the period. Instead, you should import the Complete Date as a measure which will create Issues with complete date measure that will group issues that have the complete date and assign to the corresponding date on Time dimension.

Additionally, in the filter you can add to get the current month:

Avg(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    ([Measures].[Issues with complete date], -- issues with complete date
     [Time].[Month].CurrentDateMember, -- in current month
     [Status].[Done])>0 -- and in status done
  ),
...

Lauma / support@eazybi.com