Help calculating cumulative data

[GilesWalker]

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

[Rémi]

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] )

[konstantinos]

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] ) )
)

[GilesWalker · Accepted solution]

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.

Thanks for your help though.