How to get cumulative sum?

Questions & Answers
1

Hi,

I am trying get cumulative sum, but get some unexpected result. Why its happens and how to get cumulative sum? As example in calculated member i write just 1, so i get result everywhere 1.

Then i create new calculated member:

Sum(
{ PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember },
[Measures].[T_E_S_T]
)

Example
Example.png1371×648 8.33 KB

I want to get 1,2,3,4,5,6,7… and so on.

2

Function PreviousPeriods works in an optimized way to calculate cumualtive values. For month report, it will sum any previous month within a same quarter, them sums any previous quarter within a year and then sums any previous year.

If you have the formula, you are using fixed value 1 as formula for cumulative values it will give you 1 for a quarter or year as well.

Here is how you can calculate this counter.
if you are using eazyBI version 5. or older (or on cloud) with Time dimension on Rows we have a default option Add calculated you can apply to any measure and it will calculate a cumulative sum of any measure without additional calculations.

For your report, you are using Time columns. You would like to address members of the same Time hierarchy level you have in a report and sum up from the first member until each column (current member):

SUM (
{[Time].CurrentHierarchy.Level.Members.Item(0):
 [Time].CurrentHierarchyMember},
 [Measures].[T_E_S_T]
)

Daina / support@eazybi.com

3

Thank you very much for your reply.

4

Hi @Erik1 and @daina.tupule,
Thanks so much you both for bringing this topic up and for your contribution + solutions.

Would there be a way (or what MDX formula would be the best one) to get a weekly count similar to Erik’s but adding 1 every week instead of every month?

For example if the project started in week 20 (or if you want to start counting from week 20), to increment the count by 1 every following week. So:
Week 20 = 1, week 21 = 2, week 23 = 3 and so on…

I get some results with the following formula but I need to select the Week level in the Time weekly hierarchy, otherwise it count days instead:
Sum(
{ [Time.Weekly].CurrentHierarchyMember.Level.Datemember(“Jul 04, 2019”):
[Time.Weekly].CurrentHierarchyMember }, 1
)

Many thanks in advance!

5

For this calculation, you would like to use Week level explicitly in the formula. The formula below will count weeks from Jul 04, 2019 till the week represented by selected time period:

Sum(
{ [Time.Weekly].[Week].DateMember("Jul 04, 2019"):
  [Time.Weekly].[Week].DateMember([Time].CurrentHierarchyMember.StartDate) }, 1
)

In the first member, I addressed the week level in weekly hierarchy explicitly for Jul 04, 2019.

In the second member, I transferred the selected time period (for example, days) to a particular week. You can also use NextStartDate instead of StartDate if you are using the formula with months and would like to include all weeks of a selected month.

Daina / support@eazybi.com

1 Like
6

Oh whoa! Thanks so much @daina.tupule for such a quick and well explained solution!
That was really great!
Many many thanks!

7

Hi

I am able to do cumulative but I am particularly in need of fetching the final cumulative value and assign to another variable.

Is it possible in easyBI? if so how? Please let me know.

Created like this

MAX (

[Measures][Cumulative CBB]
)

But it should up the maximum of cumulative values right, but its just showing all the cumulative values.

Idea is to pick up one single value out of list of cumulative values.

Thanks,
Gopal

8

Hi I want to do something very similar but using sparklines.
So what I have is Houers booked
SparklineData(LastPeriods(6,
IIf([Time.Weekly].CurrentHierarchyMember IS
[Time.Weekly].CurrentHierarchy.DefaultMember,
[Time.Weekly].[Week].CurrentDateMember,
[Time.Weekly].CurrentHierarchyMember)

)
,[Measures].[Effort spent [MD]] (Epic Level)]
)

This gives me a point for each week. But what I would like to have is that in each week it will be sum of previous weeks’ periods. It would be like a running sum