Use the last nonempty value in multiple columns for a calculated member

I have constructed the below table to track the running total of the number of Jira Bugs created and resolved over time.

The third column is intended to track the total number of unresolved Bugs on that date. Theoretically this calculation would be: Last Value(Cumulative bugs created) - Last Value(Cumulative bugs resolved)

The calculation for the Unresolved bugs column is fine until Jul 06 when I expect the value in the Unresolved bugs column to calculate as (5 - 2) = 3. Jul 07 should be (7 - 2) = 5.

How would I grab the last nonempty value in those columns?

Hi @Kesta.Suarez,

I would suggest taking a different approach - if you use the standard measure Open issues, it shows the cumulative issues created minus cumulative issues resolved. If you only wish to look at issue type bug, you can use a tuple for specifying Unresolved bugs:

  [Measures].[Open issues],
  [Issue Type].[Bug]

Lauma /

Thank you, this is easier. Because using a tuple alone ended up generating a value for every date (and more confusingly, future dates) I revised it:

WHEN NOT IsEmpty([Measures].[Cumulative bugs created]) OR NOT IsEmpty([Measures].[Cumulative bugs resolved]) THEN
  Aggregate([Measures].[Open issues], [Issue Type].[Bug])

This generated a cleaner table of values (note how the date skips):

While this did resolve my immediate need, I am still curious about the heart of my original question in case I need this for the future. How might I grab the last nonempty value in those columns?

Hi @Kesta.Suarez ,

Great! In this demo report, you can also see an approach for displaying Open issues until today: Open Issues Trend - Issues - Jira Demo - eazyBI.

But, while there could be calculations to get the last value, to get also values in the empty cells, I would suggest the standard calculation Cumulative sum including empty. This would give Bugs created cumulative for the 6th of July as 5 as well.

Lauma /