Sum of ROM (Days) Are Not Completed from Previous Month vs Current Month

Questions & Answers
, , , ,
1

Hi,
Can someone please advise how to build MDX code to sum up a custom number field called ROM (Days) that are not done/completed from the previous month and another for the current month. I can’t use [Measures].[ROM (Days) created] because it’d only specify what was created in a given month vs. the entire universe of ROM (Days) currently outstanding. I tried with this code:

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
NOT (
[Measures].[Issue status] MATCHES ‘Done|Canceled|Deferred’
)
),
(
[ROM (Days)],
[Time].[Current month]
)
)

Any help is appreciated, thanks

2

Hi @Keiko

In this case, I would recommend using historical statuses and tuples.
First, make sure that “ROM (Days)” numeric field is imported with change history.

Then use this formula for calculated measure:

[Measures].[ROM (Days) history]
-
Aggregate({
  [Transition Status].[Done],
  [Transition Status].[Canceled],
  [Transition Status].[Deferred]
  },
  [Measures].[ROM (Days) history]
 )

It will work with your calculated Time dimension periods and show the historical sum (at the end of particular period) of ROM (Days) for issues were not in the closed statuses at the end of each period.

Martins / eazyBI

3

Thanks Martins, this is super helpful!