How do I create a cumulative sum over multiple columns?

Question

I have a table in Power BI desktop that I'm trying to create a cumulative sum on. This is the SQL to create this table:create table #sample
(
DOS_Month               varchar(6)
,CRD_Month              varchar(6)
,Credit_Received_Date   date
,DaysElapsed            varchar(20)
,Last_Mo_Collections    decimal(13,0)
)
insert #sample values('201707','201708','8/2/2017','01',11470)
insert #sample values('201707','201708','8/3/2017','01',2821)
insert #sample values('201707','201708','8/4/2017','01',1361)
insert #sample values('201707','201708','8/7/2017','01',9040)
insert #sample values('201707','201708','8/3/2017','02',2397)
insert #sample values('201707','201708','8/4/2017','02',5101)
insert #sample values('201707','201708','8/7/2017','02',2256)
insert #sample values('201707','',NULL,'Complete Month',1041764)After adding the cumulative sum column, this table should look like this:&nbsp;DOS_Month   CRD_Month   Credit_Received_Date    DaysElapsed Last_Mo_Collections Cumulative
$201,707    201708  8/2/2017    1   $11,470 $11,470
$201,707    201708  8/3/2017    1   $2,821  $14,291
$201,707    201708  8/4/2017    1   $1,361  $15,652
$201,707    201708  8/7/2017    1   $9,040  $24,692
$201,707    201708  8/3/2017    2   $2,397  $27,089
$201,707    201708  8/4/2017    2   $5,101  $32,190
$201,707    201708  8/7/2017    2   $2,256  $34,446
$201,707            Complete Month  $1,041,764  $1,076,210How can I do this using DAX or Quick Measures? Also, I can't use the time functions in order to sum these either since DOS_Month and CRD_Month aren't time datatypes.&nbsp;

anonymous · Accepted Answer

ironryan77，
 After importing the SQL table to Power BI Desktop, add an index column to the table in Query Editor.
&nbsp;
Then create the Cumulative measure using the DAX below.
Cumulative = CALCULATE(SUM('sample'[Last_Mo_Collections]),FILTER(ALL('sample'),'sample'[Index]&lt;=MAX('sample'[Index])))   Regards,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

How do I create a cumulative sum over multiple columns?

1 Reply

Related content

Cumulative Total

Cumulative formula for multiple columns

Sequential Order column based on multiple criteria

Cumulative Sum over multiple columns

Multiple line and clustered columns chart