Hello, my requirement is to count number of parent issues, where the difference between two date fields is less than or equal to 10 working days. Also, we want to count with the same condition (<=10 working days) numbers against other attributes like labels/type/priority/other custom fields and not just issues.
For this I have used the following formula:
NonZero(Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
(
DateDiffWorkdays([Measures].[Date of Request],[Measures].[Prep Complete Date])
) <= 10)
)
)
This works good only when the dimension “Issue” is used. But if we use any other dimension apart from “Issue”, counts are wrong.
I will be glad to get help in correcting the formula or correct my approach. Thank you.
Your calculation is missing the numerical measure for the report context.
When you add more than “Issue” dimensions, the context is important for the calculation.
Make sure that both date picker fields “Date of Request” and “Prep Completion date” are imported as properties and measures.
Then use a numerical measure for your calculation.
NonZero(Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
(
DateDiffWorkdays([Measures].[Date of Request],[Measures].[Prep Complete Date])
) <= 10
AND
[Measures].[Issues created]>0
)
)
)
Note the measure “Issues created’” is used in the filter conditions.
Now, the calculation would count only issues that meet the filter criteria.