Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |