Comparing current month cumulative figures against previous month, filtered by issue status

Questions & Answers
1

image
I’m trying to do something like the above image, where there is a value for the number of cumulative open issues for the current month, and compared to the previous month, with the difference, and an arrow indicator to show whether the value has gone up or down.
“Open” issues is further filtered by several statuses.
So for the current month, I’m using

Aggregate({
  [Time].[Month].CurrentDateMember,
  [Status].[Waiting for customer],
  [Status].[Work in progress],
  [Status].[L2 Review]
})

But I’m having trouble getting the value for the previous month, using the code below yields the same value as current month.

Aggregate({
  [Time].[Month].CurrentDateMember.PrevMember,
  [Status].[Waiting for customer],
  [Status].[Work in progress],
  [Status].[L2 Review]
})
2

Hi @Tony_Chung

Welcome to the eazyBI community!

Start report building in a table view, in the following way…

  1. Select, in the report Rows, your project.
  2. In Columns, use a measure that shows issues in particular statuses at the moment. As you would also need this value for past periods (the previous month for a comparison), historical measures should be used.
Sum({
  [Transition Status].[Waiting for customer],
  [Transition Status].[Work in progress],
  [Transition Status].[L2 Review]
 },
  ([Measures].[Issues history], 
   [Time].[Month].CurrentDateMember)
)

For calculating changes, create another measure (let’s call it “Open issues change”, we will need the name in the next measure)

Sum({
    [Transition Status].[Waiting for customer],
    [Transition Status].[Work in progress],
    [Transition Status].[L2 Review]},
 ([Measures].[Issues history], [Time].[Month].CurrentDateMember)
 - 
 ([Measures].[Issues history], [Time].[Month].CurrentDateMember.PrevMember)
)
  1. Then you would create another measure with markdown formatting to display arrow up for positive values and arrow down for negative values. Add it also in the columns.
case WHEN
  [Measures].[Open issues change] > 0
THEN
  "<i class='far fa-arrow-up' style='color:red'></i>"
ELSE
  "<i class='far fa-arrow-down' style='color:green'></i>"
END

Important: set formatting Text > Markdown to see symbols!

  1. Use Gauge chart view, option “Values only”

image
image2386×672 59.8 KB

Here is one more example of similar report:
https://eazybi.com/accounts/1000/cubes/Issues/reports/502235-project-overview

It is possible to create a bit more complex measure where you concatinate together actual change value and the arrow in one cell, but then this value won’t be drillable (it would be a string).
Here is an example of such a measure (it is one measure): Project overview based on Sprints and Story Points - formatted - Issues - Jira Demo - eazyBI

Best,
Ilze , support@eazybi.com