How to create a cumulative sum over multiple columns in Power BI?

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:

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.

Joe G · Accepted Answer · 2017-08-09 00:51:18Z

1

This formula should give you your desired cumulative sum column.

Cumulative = CALCULATE(
    SUM(Table1[Last_Mo_Collections]), 
    FILTER(
        Table1, 
        Table1[DOS_Month] = EARLIER(Table1[DOS_Month]) &&
        (
            (
                Table1[DaysElapsed] = EARLIER(Table1[DaysElapsed]) && 
                Table1[Credit_Received_Date] <= EARLIER(Table1[Credit_Received_Date])
            ) || (
                Table1[DaysElapsed] < EARLIER(Table1[DaysElapsed])
            )
        )
    )
)

The EARLIER function is the key (and in my opinion horribly named). From the documentation, the function...

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

answered Aug 9, 2017 at 0:51

Joe G

1,7961 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a cumulative sum over multiple columns in Power BI?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related