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

[Keiko Kamiya]

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

[eazyBI Support]

Hi @Keiko Kamiya 

In similar way you can use "Issues history" to calculate the count of issues in the historical status at the end of each period. It would work in same way as "ROM history" measure.

If your formula doesn't work without "Time" dimension, likely something is wrong with the calculated member "Previous Month" in "Time" dimension Or maybe it is expecting a "Time" dimension to be present in report.

What formula did you use for calculated member "Previous month" in "Time" dimension?

In latest versions of eazyBI (and also cloud) you could ask the help of our AI assistant to build your MDX formula for you

You can also try this formula for hardcoded Prev Month calculation. Now it doesn't require the Time dimension in report anymore

Sum(
{
[Transition Status].[Done],
[Transition Status].[Canceled],
[Transition Status].[Deferred]
},
(
[Measures].[ROM history],
[Time].[Month].CurrentDateMember.PrevMember
)
)

And similar for CurrentMonth

Sum(
{
[Transition Status].[Done],
[Transition Status].[Canceled],
[Transition Status].[Deferred]
},
(
[Measures].[ROM history],
[Time].[Month].CurrentDateMember
)
)

Martins / eazyBI

[eazyBI Support]

Hi @Keiko Kamiya 

You might want to check that "ROM" number field is imported with value change history from the import options page:

https://docs.eazybi.com/eazybi/data-import/data-from-jira#DatafromJira-JiraCustomFields

This would create new measure "ROM history" that returns the historical value at the end of particular time period (Previous month in your use-case)

Then create new calculated measure using this MDX formula:

Sum(
{
[Transition Status].[Done],
[Transition Status].[Canceled],
[Transition Status].[Deferred]
},
(
[Measures].[ROM history],
[Time].[Previous month]
)
)

And similar for "Current month" period.

This formula should return the historical ROM value at the end of period for issues in historical statuses.


Martins from eazyBI support

[Keiko Kamiya]

Hi Martins,

Thank you for your insight! I think the ROM (history) measure is what I'm looking for. Is that equivalent to when you go to issue history, you'll see what status that issue was at a particular time?

I created and enabled the measure you described above, however, no data populates unless I enable the Time measure but that would double the data:

For ex: 

test1 created measure is for sum of ROM in progress only previous month

test2 created measure is for sum of ROM in progress only in current month

Below is what it shows if I enable the Time measure:

**If I disable the Current month or Previous month Time measures, the data disappears. 

Also, looking to retrieve sum of ROM not completed so is the below the appropriate coding:

Sum(
Filter(
[Transition Status].[Transition Status].Members,
[Transition Status].CurrentHierarchyMember.Name <> "Done"
AND [Transition Status].CurrentHierarchyMember.Name <> "Canceled"
AND [Transition Status].CurrentHierarchyMember.Name <> "Deferred"
),
(
[Measures].[ROM history],
[Time].[Previous month]
)
)

Thank you for your help in advance! 

[Keiko Kamiya]

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