# Power BI Exchange

View Only

## Cumulative sum by data and project

• #### 1.  Cumulative sum by data and project

Posted Oct 06, 2019 04:12 PM
Hi everybody,

I was trying for a few days now to calculate a cumulative sum of all entries up on each project from a list.
Basically i have below table(X) with repeating project names that have values for different periods.
I would want to have a calculated row that for each line and project adds up previous entries (values), so from previous periods.
It's similar to a cumulative sum but is per project, and the entries are not necesarily sorted by period.
I've tried some combinations of Calculate(SUM (X[Value], Filter( All( X[Project], X[Period]<=Earlier(X[Period]) or MAX but no result.

 Project Period Value Cumulative row A 201901 0 0 A 201902 0 0 A 201903 0 0 A 201904 1 1 A 201905 1 2 A 201906 1 3 B 201902 1 1 B 201903 1 2 B 201904 0 2 B 201905 1 3 B 201906 0 3 C 201901 1 1 C 201902 1 2 C 201903 0 0 C 201904 1 3

Any ideas on how to make it work?

------------------------------

------------------------------

Attachment(s)

Workfile.xlsb   19 KB 1 version

• #### 2.  RE: Cumulative sum by data and project

Bronze Contributor
Posted Oct 07, 2019 02:21 AM
I've attached a spreadsheet of how I would approach the problem. The measure I thought you may require looks like this.
```=CALCULATE( SUM('Input Table'[Value]),
FILTER(
ALLEXCEPT('Input Table', 'Input Table'[Project]),
'Input Table'[Period]<=MAX( 'Input Table'[Period])
)
)​```

Steve.

------------------------------
Steven Doherty
Director
Market Grunt
Melbourne VI
418192168
------------------------------

Attachment(s)

• #### 3.  RE: Cumulative sum by data and project

Posted Oct 07, 2019 04:06 PM
Hi Steve,
Many thanks for your support. It works like a charm.
Now i'll have to see how it behaves to a 200k database.

------------------------------
400439
------------------------------

• #### 4.  RE: Cumulative sum by data and project

Bronze Contributor
Posted Oct 07, 2019 06:43 PM

I'm glad it does the trick although, I always forget to ask before offering a solution, "How many rows are in the data table?". A 200K row data table shouldn't be a concern depending on the granularity of the data and the width of the table. From what I can see, the date field appears to be the most granular item, and this shouldn't be a problem.

If it is an issue I would suggest introducing a Var into the calculation to narrow the table passed to the Filter() function, perhaps something like this;

```Cum Total by Project V2 =
Var Project_Table =
Calculatetable(
Summarize(Input Table,
'Look Up Table'[Project ID],
'Date Table'[Date],
'Input Table'[Value]),
Allexcept('Look Up Table',[Project ID])
)
Return

Calculate(
Sum('Input Table'[Value]),
Filter( Project_Table,
'Date Table' [Date]<= Max('Date Table'[Date])
)
)```

------------------------------
Steven Doherty
Director
Market Grunt
Melbourne VI
0418192168
------------------------------