Calculated measure with backdated date field (JIRA)

[shaakunthala]

Hi eazyBI community,

I’m new here and this is my first question.

I’m trying to define a new calculated measure for the following case:

We have a date custom field named ‘Received Date’.
“Number of issues where Received Date is prior the current quarter, but still in the In Progress status”
The idea is to get the count of issues that are carried forward from previous quarters.

For this, I’m using Status dimension in Rows, and Time dimension in Columns. Here’s the pseudo code of what I’m trying to achieve:
The approach I’m considering is creating a duplicate of the [Measures].[Issues with received date] measure from scratch but with backdated Received Date.

My question:

• Is this the best approach?
• If so, how do I create a measure from the scrath?

Thanks,
Shaaku

[shaakunthala]

So far, I managed to get it resolved with the help of the following question/answer:

Count issue with a date measure is minor that date row

Hi, I try to obtein the number of issues when the value “fecha de entrega” is minor that de date´s row. [image] I created a new measure like this, but doesn´t work. NonZero( Count( Filter( Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]), Not IsEmpty([Measures].[Issue Fecha de entrega]) --custom field imported as property and filter AND [Measures].[Issue Fecha de entrega] <= [Time].DefaultMember --numeric measure for filter ) ) ) Which is the error? Thanks! BR

[Measures].[Carried forward] = 

NonZero(
  Count(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      NOT IsEmpty([Issue].CurrentHierarchyMember.get('Received Date'))
      AND DateCompare(
        [Issue].CurrentHierarchyMember.get('Received Date'),
        [Time].CurrentHierarchyMember.StartDate
      ) <= 0
	  AND NOT IsEmpty([Issue].CurrentHierarchyMember.get('Submission Date'))
	  AND DateCompare(
		[Issue].CurrentHierarchyMember.get('Submission Date'),
		[Time].CurrentHierarchyMember.StartDate
	  ) > 0
    )
  )
)

Notes:

• I could not use [Measures].[Issue Received Date] property directly becuase I have formatted it in ISO date format.
• Understandably in my case, we also have a field named Submission Date. The case was corrected as “Number of issues where Received Date is prior the current quarter, but submitted during the current quarter or in the future”. In Progress status was not taken into account.

Thanks to @jlopmor and eazyBI staff @ilze.leite for the answer in the mentioned thread.

Regards,
Shaaku