User Defined Calculation not totaling

I have created a custom user defined measure called DueNextYear. The formula for this measurement is:
CASE
DateBetween(
[Issue].CurrentHierarchyMember.get(‘Completion Due Date’),
Now(),
‘next year’
)
WHEN CBool(1) THEN 1
WHEN CBool(0) THEN 0
END

However, when this is generated in a chart and I ask that the rows and columns be totaled only the columns are totaled (see screen shot below) and the row total returns zero. Why would this be? Also the values aren’t rolled up to the hierarchies.

Hi @mcondon77!

I just wrote you through eazyBI support mail as well, but following is a summary for the community as well.

The Total row does not have Completion Date, which is why there is no value displayed for it. You would need to add some aggregation function, e.g., Sum(), to add up the true or false values.

I would suggest a different approach though by using the measure created for the custom date field together with the Time dimension. Please try the following:

Sum(Filter(
  Descendants([Time].CurrentHierarchyMember, [Time].[Day]),
  DateBetween([Time].CurrentHierarchyMember.StartDate,
  Now(), "one year from now")
), [Measures].[Issues with Completion Due Date])

Lauma / support@eazybi.com

Thank you for the feedback. I’ll take a look and let you know if I have anymore questions. :slight_smile:

1 Like