Cumulative Sum

[Jcho10]

Hi everybody,

I'm learning PBI and I would like to create a matrix table to calculate :

-In a first column : the total turnover by month

-In a second column : the cumulative turnover

I would like to get this result :

MONTH	TURNOVER	CUMULATIVE TURNOVER
1	84 693	84 693
2	190 685	275 378
3	60 935	336 313
4	119 286	455 599
5	44 570	500 169
6	47 644	547 813
7	130 880	678 693
8	59 472	738 165
9	48 321	786 486
10	52 564	839 050
11	16 312	855 362
12	57 059	912 421

I've created a measure TURNOVER which works well :

TURNOVER = 
VAR
DebutCA = SUMMARIZE('Parametres comptes';'Parametres comptes'[DebutCA])
VAR
FinCA = SUMMARIZE('Parametres comptes';'Parametres comptes'[FinCA])
VAR
JournalVentes = SUMMARIZE('Parametres journaux';'Parametres journaux'[JournalVentes])
RETURN
CALCULATE(sum('Journaux 3 ans'[Solde]);
'Journaux 3 ans'[Compte]>=DebutCA;
'Journaux 3 ans'[Compte]<=FinCA;
'Journaux 3 ans'[Journal]=JournalVentes;
)

I would like to create a measure to calculate cumulative turnover.

I don't know how to proceed.

I tried differents measures but it doesn't works...

Thank you for your help.

Sincerely

[v-haibl-msft · Accepted solution]

@Jcho10

In this scenario, you need to create a Calendar table first with following formula. And create relationship between the Calendar table with the fact table with Date key.

Calendar = 
CALENDAR ( "1/1/2015", "12/31/2016" )

Since I don’t know your table structure, I just create a simple fact table as below. The Measure expression for TURNOVER is “TURNOVER = SUM( Table1[Sold] )”.

Then we can create a Measure with following formula to get the cumulative turnover.

CUMULATIVE TURNOVER = 
CALCULATE (
    [TURNOVER],
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

You can take a look at this document to understand more about Cumulative Sum.

I've also uploaded my .pbix file here for reference.

Best Regards,

Herbert

[Jcho10]

Hi Herbert Liu,

Thank you, it works better with a calendar table !!

I have a second question.

I would like to create 2 tables to compare turnover by month in differents years.

I've created in my calendar date a column with Text Month Name and this measure :

Turnover by month "TURNOVER = SUM( Table1[Sold] "

This table works well

But I don't know how to create the 2nde table to have "Turnover cumulate by month"

 

Because running total with text month doesn't works...

Do you have an idea ?

Thank very much for helping me

Sincerely