Help with conditional issue counting based on dates and ECDs

I’m attempting to create a type of dynamic cumulative chart based on due dates compared to the current date. My custom measure isn’t working, however, and I’m not sure why. All I get are blanks.

My goal is to have a kind of burn-up chart showing when all issues will be done based on their status, due dates, and a custom ECD date field. The general idea I’m trying to achieve is:

  • For any time row in the past or present, count the number of issues in “Pending” or “Closed” status
  • For any time row in the future check if an issue has the ECD field filled out
    ** If yes, count that issue in the time row corresponding to the ECD date
    ** If no, count that issue in the time row corresponding to the due date

The idea is in the past, count actual issues pending or closed. In the future, count issues on their due dates. Issues past due (due date in past, but not in pending or closed) should have the ECD field filled with a date in the future, so count that date instead of due date.

This code is not working, the measures work on their own but not here. Any ideas?

	CASE
	WHEN DateCompare([Issue].CurrentHierarchyMember.Get('Due date'), "Today") <= 0 THEN
		  [Measures].[IssuesInPending]
	WHEN DateCompare([Issue].CurrentHierarchyMember.Get('Due date'), "Today") > 0 
          AND NOT IsEmpty([Issue].CurrentHierarchyMember.get('ECD')) THEN
		      [Measures].[Issues with ecd]
	WHEN DateCompare([Issue].CurrentHierarchyMember.Get('Due date'), "Today") > 0 
          AND IsEmpty([Issue].CurrentHierarchyMember.get('ECD')) THEN
		      [Measures].[Issues due count]
	END

Hi @afgnebulous

Properties like “Due Date” and “ECD” would return values only when used at “Issue” level that is why your calculations doesn’t work with “Time” dimension without using also “Issue” dimension in report.

Here you would need to create a calculated measure that iterates through “Issue” dimension member for each Time period in report rows.

Try this formula for your calculated measure:

CASE WHEN
DateAfterPeriodEnd('today',[Time].CurrentHierarchyMember)
OR
DateInPeriod(
  'today',
  [Time].CurrentHierarchyMember
)
THEN
Aggregate(
  {
    [Status].[Pending],
    [Status].[Closed]

  },
  [Measures].[Issues created]
)
ELSE
[Measures].[Issues with ECD]
+
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
IsEmpty([Measures].[Issue ECD])
),
CASE WHEN
[Measures].[Issues with due date]>0
THEN
1
END
)
END

Martins / eazyBI