How to use last non-empty value for cumulative measure?

Questions & Answers
1

Hello all,

I want to created multiple trend lines for a cumulative measure. To do so, I want to create the following table (simplified)

image
image771×159 3.99 KB

I started with this measure and created the following table:
Measure definition:

Count(Filter
      (
        --iterate through set of issues
        Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
        --apply filter criteria to each issue
        [Measures].[Issues created] > 0), 
       -- numeric expression - sum of relevant issues
      [Measures].[Issue QBD 900 links] = 1
      ) 
   )

Based on this, I created this cumulative measure:

CASE WHEN [Measures].[qbd-qbd-900] > 0
THEN

Sum(
  PreviousPeriods([Time].CurrentHierarchyMember),
  [Measures].[qbd-qbd-900]
)
END

However, this gives me the following - i.e. empty cells for the row with 0 QbD-900.

image
image817×181 3.69 KB

I tried the out of the box measure for creating a cumulative measure including empty cells but that doesn’t give the desired result.

Does anybody have an idea how to solve this?

Thanks in advance!

2

Hi @bvanloenen,

Your expression does not display any value for April because of the “CASE WHEN” condition.
You might as well remove the condition and keep the part with the Sum of PreviousPeriods.

Sum(
  PreviousPeriods([Time].CurrentHierarchyMember),
  [Measures].[qbd-qbd-900])

Could you please confirm if you tried adding the standard calculation Cumulative sum, including empty, to this measure?
If the standard cumulative sum did not work, could you explain why it did not fit your needs?

Regards,
Oskars / support@eazyBI.com