Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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,
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,
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
71 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |