cancel
Showing results for
Did you mean:
Skilled Sharer

Help calculating cumulative data

Hi Everyone,

I have a table where I need to calculate the cumulative of Forestry total wagons column in the data below. The number in this column is derived from multiplying columns forestry wagons and forestry services. For example in September the max number of wagons is 189. In October It was 198 for some of the month and 117 for the rest, average is 179. Therefore the cumulative number for these two months is 377. I cant seem to get this answer though. Hope you can help.

 Date key Year Month Days Total days Week Forestry services Months Quarter Forestry wagons Forestry target tonnes Forestry wagon KPI tonnes Forestry total wagons 1/09/2015 2015 9 1 30 36 21 Sep Q3 9 6237 32 189 2/09/2015 2015 9 2 30 36 21 Sep Q3 9 6237 32 189 3/09/2015 2015 9 3 30 36 21 Sep Q3 9 6237 32 189 4/09/2015 2015 9 4 30 36 21 Sep Q3 9 6237 32 189 5/09/2015 2015 9 5 30 36 21 Sep Q3 9 6237 32 189 6/09/2015 2015 9 6 30 37 21 Sep Q3 9 6237 32 189 7/09/2015 2015 9 7 30 37 21 Sep Q3 9 6237 32 189 8/09/2015 2015 9 8 30 37 21 Sep Q3 9 6237 32 189 9/09/2015 2015 9 9 30 37 21 Sep Q3 9 6237 32 189 10/09/2015 2015 9 10 30 37 21 Sep Q3 9 6237 32 189 11/09/2015 2015 9 11 30 37 21 Sep Q3 9 6237 32 189 12/09/2015 2015 9 12 30 37 21 Sep Q3 9 6237 32 189 13/09/2015 2015 9 13 30 38 21 Sep Q3 9 6237 32 189 14/09/2015 2015 9 14 30 38 21 Sep Q3 9 6237 32 189 15/09/2015 2015 9 15 30 38 21 Sep Q3 9 6237 32 189 16/09/2015 2015 9 16 30 38 21 Sep Q3 9 6237 32 189 17/09/2015 2015 9 17 30 38 21 Sep Q3 9 6237 32 189 18/09/2015 2015 9 18 30 38 21 Sep Q3 9 6237 32 189 19/09/2015 2015 9 19 30 38 21 Sep Q3 9 6237 32 189 20/09/2015 2015 9 20 30 39 21 Sep Q3 9 6237 32 189 21/09/2015 2015 9 21 30 39 21 Sep Q3 9 6237 32 189 22/09/2015 2015 9 22 30 39 21 Sep Q3 9 6237 32 189 23/09/2015 2015 9 23 30 39 21 Sep Q3 9 6237 32 189 24/09/2015 2015 9 24 30 39 21 Sep Q3 9 6237 32 189 25/09/2015 2015 9 25 30 39 21 Sep Q3 9 6237 32 189 26/09/2015 2015 9 26 30 39 21 Sep Q3 9 6237 32 189 27/09/2015 2015 9 27 30 40 21 Sep Q3 9 6237 32 189 28/09/2015 2015 9 28 30 40 21 Sep Q3 9 6237 32 189 29/09/2015 2015 9 29 30 40 21 Sep Q3 9 6237 32 189 30/09/2015 2015 9 30 30 40 21 Sep Q3 9 6237 32 189 1/10/2015 2015 10 1 31 40 22 Oct Q4 9 6534 32 198 2/10/2015 2015 10 2 31 40 22 Oct Q4 9 6534 32 198 3/10/2015 2015 10 3 31 40 22 Oct Q4 9 6534 32 198 4/10/2015 2015 10 4 31 41 22 Oct Q4 9 6534 32 198 5/10/2015 2015 10 5 31 41 22 Oct Q4 9 6534 32 198 6/10/2015 2015 10 6 31 41 22 Oct Q4 9 6534 32 198 7/10/2015 2015 10 7 31 41 22 Oct Q4 9 6534 32 198 8/10/2015 2015 10 8 31 41 22 Oct Q4 9 6534 32 198 9/10/2015 2015 10 9 31 41 22 Oct Q4 9 6534 32 198 10/10/2015 2015 10 10 31 41 22 Oct Q4 9 6534 32 198 11/10/2015 2015 10 11 31 42 22 Oct Q4 9 6534 32 198 12/10/2015 2015 10 12 31 42 22 Oct Q4 9 6534 32 198 13/10/2015 2015 10 13 31 42 22 Oct Q4 9 6534 32 198 14/10/2015 2015 10 14 31 42 22 Oct Q4 9 6534 32 198 15/10/2015 2015 10 15 31 42 22 Oct Q4 9 6534 32 198 16/10/2015 2015 10 16 31 42 22 Oct Q4 9 6534 32 198 17/10/2015 2015 10 17 31 42 22 Oct Q4 9 6534 32 198 18/10/2015 2015 10 18 31 43 22 Oct Q4 9 6534 32 198 19/10/2015 2015 10 19 31 43 22 Oct Q4 9 6534 32 198 20/10/2015 2015 10 20 31 43 22 Oct Q4 9 6534 32 198 21/10/2015 2015 10 21 31 43 22 Oct Q4 9 6534 32 198 22/10/2015 2015 10 22 31 43 22 Oct Q4 9 6534 32 198 23/10/2015 2015 10 23 31 43 22 Oct Q4 9 6534 32 198 24/10/2015 2015 10 24 31 43 22 Oct Q4 9 6534 32 198 25/10/2015 2015 10 25 31 44 13 Oct Q4 9 3861 32 117 26/10/2015 2015 10 26 31 44 13 Oct Q4 9 3861 32 117 27/10/2015 2015 10 27 31 44 13 Oct Q4 9 3861 32 117 28/10/2015 2015 10 28 31 44 13 Oct Q4 9 3861 32 117 29/10/2015 2015 10 29 31 44 13 Oct Q4 9 3861 32 117 30/10/2015 2015 10 30 31 44 13 Oct Q4 9 3861 32 117 31/10/2015 2015 10 31 31 44 13 Oct Q4 9 3861 32 117
1 ACCEPTED SOLUTION
Skilled Sharer

Thanks for the help everyone, unfortunately I didnt get the right answer from your responses. The table I am using is just an excel file I manually created so I went in there and for the end of each month using an if formula I created a one off total wagons number. This worked perfectly.

3 REPLIES 3
Resolver III

Hi,

Can you try this measure :

Cumulative Measure = SUMX( FILTER( SUMMARIZE( YourTable , [Month] , "Total wagons" , AVERAGE( [Forestry total wagons] ) , "Max Date" , MAX( [Date Key] ) , [Max Date]<=MAX([Date Key]) ) , [Total wagons] )

Memorable Member

Hi also this works..I think @Rémi must be faster

```cummulative :=
CALCULATE (
SUMX (
VALUES ( Table1[Months] );
AVERAGEX ( Table1; Table1[Forestry total wagons] )
);
FILTER ( ALL ( Table1 ); Table1[Date key] <= MAX ( Table1[Date key] ) )
)```

Edit : You don't need the AVERAGEX iteration..simple Average

```cummulative :=
CALCULATE (
SUMX ( VALUES ( Table1[Months] ); AVERAGE ( Table1[Forestry total wagons] ) );
FILTER ( ALL ( Table1 ); Table1[Date key] <= MAX ( Table1[Date key] ) )
)```
Konstantinos Ioannou
Skilled Sharer

Thanks for the help everyone, unfortunately I didnt get the right answer from your responses. The table I am using is just an excel file I manually created so I went in there and for the end of each month using an if formula I created a one off total wagons number. This worked perfectly.

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors