Filter based on past status

Questions & Answers
1

Hi,

I am trying to create some reports based on whether scheduled issues were completed by their target end date. I am quite new to eazyBI and MDX and finding it very difficult to use for anything more than trivial charts.

I have enabled importing issue history and the ‘Target start’ and ‘Target end’ fields as both measures and properties. I first want to create a timeline chart showing how many issues had a ‘Target end’ date in each month, but only for issues that have ever been in the status ‘Scheduled’.

I have tried every variation I can think of for how to express this as a calculated measure, with no success.

2

Hi dqng,
Welcome to eazyBI community! MDX can indeed feel very non-trivial at first, especially when you introduce issue history.

Since you have imported Target end as a measure and you want to group by that date, your base measure should be Issues with Target end. However, to add more conditions like “issue was ever in status Scheduled”, you’ll need to use Descendants() and Filter() functions to iterate through individual issues and keep only the ones that match all your criteria.

Try using the following formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- Check if Target end date falls in the specific time period
    DateInPeriod(
      [Issue].CurrentMember.Get('Target end'),
      [Time].CurrentHierarchyMember
    )
    AND
    -- Check if issue was ever in 'Scheduled' status
    (
      [Measures].[Transitions to status],
      [Transition Status].[Scheduled],
      [Time].CurrentHierarchy.DefaultMember
    ) > 0
  ),
  [Measures].[Issues with Target end]
)

Let me know if that works for you or if any adjustments are needed.

Best,
Ilze // eazyBI support

3

Thank you, yes that works :slight_smile: