How do I create a cumulative sum over multiple columns?

[ironryan77]

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:

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,210

How 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.

[v-yuezhe-msft · Accepted solution]

@ironryan77，

After importing the SQL table to Power BI Desktop, add an index column to the table in Query Editor.

Then create the Cumulative measure using the DAX below.

Cumulative = CALCULATE(SUM('sample'[Last_Mo_Collections]),FILTER(ALL('sample'),'sample'[Index]<=MAX('sample'[Index])))



Regards,