EazyBI - MDX code to sum up a number field that were in progress in the previous and current months

Question

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

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

Any help is appreciated, thanks

Roberts Čāčus - eazyBI · Answer

Hi @Keiko Kamiya

Please see Martins response on the eazyBI community post, where you asked the same question regarding the original estimated hours history - https://community.eazybi.com/t/sum-of-rom-days-are-not-completed-from-previous-month-vs-current-month/19586/5.

Best,
Roberts // support@eazybi.com

Martins Vanags_eazyBI · Answer

Hi @Keiko Kamiya I can recommend using historical statuses and tuples. First, make sure that numeric custom field “ROM (Days)” imported in eazyBI 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 a particular period) of ROM (Days) for issues that were not in the three defined statuses at the end of each period. Martins / eazyBI

Keiko Kamiya_kl20q3 · Answer

Hi Martins,

Thanks for your input. When I put in the exact code you referenced above: no data gets populated (even when Time dimension enabled).

This is the built-in Time dimension formula for [Time].[Previous month]:

Aggregate({
[Time].[Month].CurrentDateMember.PrevMember
})

I removed the [Time].[Previous month] from the formula and data was retrieved with the logic below:

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
NOT (
[Measures].[Issue status] MATCHES 'Done|Canceled|Deferred'
)
),
[Measures].[ROM history]
)

and also enabled the Time dimension built-in Current month:

Aggregate({
[Time].[Month].CurrentDateMember
})

and Previous month:

Aggregate({
[Time].[Month].CurrentDateMember.PrevMember
})

Data was returned, but when I exported to Excel and added up the ROM, it didn't result in the exact sum of ROM reflected in eazyBI