Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative sum by day and category

Hello ,

 

I own a table under powerbi desktop . I want create a cumulative sum by day and category .

 

An example of data here , that you can use in CSV.  "OBJ_Date_day" and "OBJ_Date_1erdm" are under excel format (number) , you must transform this in date before to use under powerbi.

 

I have trying several functions with calculate,sum, filter, all, allexcepted , max , datesbetween without success. Can you help me to create the function ?

 

OBJ_MAX_day;CATEG;OBJ_Date_day;OBJ_Date_1erdm;OBJ_MAX_SUM_CLASS_month
90;cat1;43677;43647;2000
90;cat1;43676;43647;2000
80;cat1;43675;43647;2000
90;cat1;43672;43647;2000
90;cat1;43671;43647;2000
80;cat1;43670;43647;2000
90;cat1;43669;43647;2000
90;cat1;43668;43647;2000
80;cat1;43665;43647;2000
90;cat1;43664;43647;2000
90;cat1;43663;43647;2000
80;cat1;43662;43647;2000
90;cat1;43661;43647;2000
90;cat1;43658;43647;2000
80;cat1;43657;43647;2000
90;cat1;43656;43647;2000
90;cat1;43655;43647;2000
80;cat1;43654;43647;2000
90;cat1;43651;43647;2000
90;cat1;43650;43647;2000
80;cat1;43649;43647;2000
90;cat1;43648;43647;2000
90;cat1;43647;43647;2000
20;cat1;43644;43617;300
10;cat1;43643;43617;300
20;cat1;43642;43617;300
10;cat1;43641;43617;300
20;cat1;43640;43617;300
10;cat1;43637;43617;300
20;cat1;43636;43617;300
10;cat1;43635;43617;300
20;cat1;43634;43617;300
10;cat1;43633;43617;300
20;cat1;43630;43617;300
10;cat1;43629;43617;300
20;cat1;43628;43617;300
10;cat1;43627;43617;300
20;cat1;43623;43617;300
10;cat1;43622;43617;300
20;cat1;43621;43617;300
20;cat1;43620;43617;300
20;cat1;43619;43617;300
200;cat1;43616;43586;4000
200;cat1;43614;43586;4000
200;cat1;43613;43586;4000
200;cat1;43612;43586;4000
200;cat1;43609;43586;4000
200;cat1;43608;43586;4000
200;cat1;43607;43586;4000
200;cat1;43606;43586;4000
200;cat1;43605;43586;4000
200;cat1;43602;43586;4000
200;cat1;43601;43586;4000
200;cat1;43600;43586;4000
200;cat1;43599;43586;4000
200;cat1;43598;43586;4000
200;cat1;43595;43586;4000
200;cat1;43594;43586;4000
200;cat1;43592;43586;4000
200;cat1;43591;43586;4000
200;cat1;43588;43586;4000
200;cat1;43587;43586;4000
90;cat3;43677;43647;2000
90;cat3;43676;43647;2000
80;cat3;43675;43647;2000
90;cat3;43672;43647;2000
90;cat3;43671;43647;2000
80;cat3;43670;43647;2000
90;cat3;43669;43647;2000
90;cat3;43668;43647;2000
80;cat3;43665;43647;2000
90;cat3;43664;43647;2000
90;cat3;43663;43647;2000
80;cat3;43662;43647;2000
90;cat3;43661;43647;2000
90;cat3;43658;43647;2000
80;cat3;43657;43647;2000
90;cat3;43656;43647;2000
90;cat3;43655;43647;2000
80;cat3;43654;43647;2000
90;cat3;43651;43647;2000
90;cat3;43650;43647;2000
80;cat3;43649;43647;2000
90;cat3;43648;43647;2000
90;cat3;43647;43647;2000
210;cat3;43644;43617;4000
210;cat3;43643;43617;4000
210;cat3;43642;43617;4000
210;cat3;43641;43617;4000
210;cat3;43640;43617;4000
210;cat3;43637;43617;4000
210;cat3;43636;43617;4000
210;cat3;43635;43617;4000
210;cat3;43634;43617;4000
210;cat3;43633;43617;4000
210;cat3;43630;43617;4000
210;cat3;43629;43617;4000
210;cat3;43628;43617;4000
210;cat3;43627;43617;4000
210;cat3;43623;43617;4000
210;cat3;43622;43617;4000
210;cat3;43621;43617;4000
210;cat3;43620;43617;4000
220;cat3;43619;43617;4000
200;cat3;43616;43586;4000
200;cat3;43614;43586;4000
200;cat3;43613;43586;4000
200;cat3;43612;43586;4000
200;cat3;43609;43586;4000
200;cat3;43608;43586;4000
200;cat3;43607;43586;4000
200;cat3;43606;43586;4000
200;cat3;43605;43586;4000
200;cat3;43602;43586;4000
200;cat3;43601;43586;4000
200;cat3;43600;43586;4000
200;cat3;43599;43586;4000
200;cat3;43598;43586;4000
200;cat3;43595;43586;4000
200;cat3;43594;43586;4000
200;cat3;43592;43586;4000
200;cat3;43591;43586;4000
200;cat3;43588;43586;4000
200;cat3;43587;43586;4000
60;cat4;43677;43647;1500
70;cat4;43676;43647;1500
60;cat4;43675;43647;1500
70;cat4;43672;43647;1500
60;cat4;43671;43647;1500
70;cat4;43670;43647;1500
60;cat4;43669;43647;1500
70;cat4;43668;43647;1500
60;cat4;43665;43647;1500
70;cat4;43664;43647;1500
60;cat4;43663;43647;1500
70;cat4;43662;43647;1500
60;cat4;43661;43647;1500
70;cat4;43658;43647;1500
60;cat4;43657;43647;1500
70;cat4;43656;43647;1500
60;cat4;43655;43647;1500
70;cat4;43654;43647;1500
60;cat4;43651;43647;1500
70;cat4;43650;43647;1500
60;cat4;43649;43647;1500
70;cat4;43648;43647;1500
70;cat4;43647;43647;1500
110;cat4;43644;43617;2000
100;cat4;43643;43617;2000
110;cat4;43642;43617;2000
100;cat4;43641;43617;2000
110;cat4;43640;43617;2000
100;cat4;43637;43617;2000
110;cat4;43636;43617;2000
100;cat4;43635;43617;2000
110;cat4;43634;43617;2000
100;cat4;43633;43617;2000
110;cat4;43630;43617;2000
100;cat4;43629;43617;2000
110;cat4;43628;43617;2000
100;cat4;43627;43617;2000
110;cat4;43623;43617;2000
100;cat4;43622;43617;2000
110;cat4;43621;43617;2000
100;cat4;43620;43617;2000
110;cat4;43619;43617;2000
100;cat4;43616;43586;2000
100;cat4;43614;43586;2000
100;cat4;43613;43586;2000
100;cat4;43612;43586;2000
100;cat4;43609;43586;2000
100;cat4;43608;43586;2000
100;cat4;43607;43586;2000
100;cat4;43606;43586;2000
100;cat4;43605;43586;2000
100;cat4;43602;43586;2000
100;cat4;43601;43586;2000
100;cat4;43600;43586;2000
100;cat4;43599;43586;2000
100;cat4;43598;43586;2000
100;cat4;43595;43586;2000
100;cat4;43594;43586;2000
100;cat4;43592;43586;2000
100;cat4;43591;43586;2000
100;cat4;43588;43586;2000
100;cat4;43587;43586;2000
870;cat2;43677;43647;20000
870;cat2;43676;43647;20000
870;cat2;43675;43647;20000
870;cat2;43672;43647;20000
870;cat2;43671;43647;20000
870;cat2;43670;43647;20000
870;cat2;43669;43647;20000
870;cat2;43668;43647;20000
870;cat2;43665;43647;20000
870;cat2;43664;43647;20000
870;cat2;43663;43647;20000
870;cat2;43662;43647;20000
870;cat2;43661;43647;20000
870;cat2;43658;43647;20000
870;cat2;43657;43647;20000
870;cat2;43656;43647;20000
870;cat2;43655;43647;20000
870;cat2;43654;43647;20000
870;cat2;43651;43647;20000
870;cat2;43650;43647;20000
870;cat2;43649;43647;20000
870;cat2;43648;43647;20000
860;cat2;43647;43647;20000
1320;cat2;43644;43617;25000
1310;cat2;43643;43617;25000
1320;cat2;43642;43617;25000
1310;cat2;43641;43617;25000
1320;cat2;43640;43617;25000
1310;cat2;43637;43617;25000
1320;cat2;43636;43617;25000
1310;cat2;43635;43617;25000
1320;cat2;43634;43617;25000
1310;cat2;43633;43617;25000
1320;cat2;43630;43617;25000
1310;cat2;43629;43617;25000
1320;cat2;43628;43617;25000
1310;cat2;43627;43617;25000
1320;cat2;43623;43617;25000
1310;cat2;43622;43617;25000
1320;cat2;43621;43617;25000
1320;cat2;43620;43617;25000
1320;cat2;43619;43617;25000
1150;cat2;43616;43586;23000
1150;cat2;43614;43586;23000
1150;cat2;43613;43586;23000
1150;cat2;43612;43586;23000
1150;cat2;43609;43586;23000
1150;cat2;43608;43586;23000
1150;cat2;43607;43586;23000
1150;cat2;43606;43586;23000
1150;cat2;43605;43586;23000
1150;cat2;43602;43586;23000
1150;cat2;43601;43586;23000
1150;cat2;43600;43586;23000
1150;cat2;43599;43586;23000
1150;cat2;43598;43586;23000
1150;cat2;43595;43586;23000
1150;cat2;43594;43586;23000
1150;cat2;43592;43586;23000
1150;cat2;43591;43586;23000
1150;cat2;43588;43586;23000
1150;cat2;43587;43586;23000

Thanks for the help

2 ACCEPTED SOLUTIONS

Hi @Anonymous ,

 

I think there’s a typo in your example , right?

 

Cat1 > 1 july > forecast 2 > sum forecast 2

Cat1 > 2 july > forecast 3 > sum forecast 5 

Cat1 > 3 july > forecast 4 > sum forecast 9

 

And I modified the formula here:

Cumulative sum = CALCULATE(SUM(Table1[Forecast]),FILTER(ALL(Table1),[Date]<=MAX([Date])),VALUES(Table1[Category]))
MIn value of the first day = CALCULATE(MIN([Forecast]),ALLEXCEPT(Table1,Table1[Category]))

000.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Anonymous
Not applicable

Hello @v-diye-msft ,

 

Yes , there is a little error in my example . You have correct this .

 

I'm blocked with you formula :

 

Cumulative sum = CALCULATE(SUM(Table1[Forecast]),FILTER(ALL(Table1),[Date]<=MAX([Date])),VALUES(Table1[Category]))
MIn value of the first day = CALCULATE(MIN([Forecast]),ALLEXCEPT(Table1,Table1[Category]))

You propose that I use a "measure". In my case i look for use a "column" , because in a second step I must link my table with an other.

 

It's possible to use a column and not a measure ?

 

Thanks for your help .

View solution in original post

6 REPLIES 6
v-diye-msft
Community Support
Community Support

Hi @Anonymous ,

 

Asuming that you’d like to calculate cumulative sum of [OBJ_MAX_day] by category and day.

 

Please use the formula:

Measure = CALCULATE(SUM('New Text Document'[OBJ_MAX_day]),FILTER(ALL('New Text Document'),[OBJ_Date_day]<=MAX([OBJ_Date_day])),ALLEXCEPT('New Text Document','New Text Document'[CATEG]))

0.png

pbix attached here for your reference: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EXID8QyWzDpAjt9Z4frL_YMBqxueVEHYU8wV69KNsXXahA?e=2VHcpY

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Hello @v-diye-msft ,

 

Thanks for your proposition but the result isn't matching with the solution that I want find.

 

For example :

 

Cat1 > 1 july > forecast 2 > sum forecast 2

Cat1 > 2 july > forecast 3 > sum forecast 5 

Cat1 > 3 july > forecast 4 > sum forecast 7

 

You know the type of result that I want find ? To complicating , I want the the result come back to min value of the first day of month when it's the first day for a category.

 

For example :

 

Cat1 > 1 july > forecast 2 > sum forecast 2

Cat1 > 2 july > forecast 3 > sum forecast 5 

Cat1 > 3 july > forecast 4 > sum forecast 7

 

The 31 july if the cumulative sum it's 250, the 1 august I want find a cumulative only for august not with a value of july.

 

Thanks for the help.

Hi @Anonymous ,

 

I think there’s a typo in your example , right?

 

Cat1 > 1 july > forecast 2 > sum forecast 2

Cat1 > 2 july > forecast 3 > sum forecast 5 

Cat1 > 3 july > forecast 4 > sum forecast 9

 

And I modified the formula here:

Cumulative sum = CALCULATE(SUM(Table1[Forecast]),FILTER(ALL(Table1),[Date]<=MAX([Date])),VALUES(Table1[Category]))
MIn value of the first day = CALCULATE(MIN([Forecast]),ALLEXCEPT(Table1,Table1[Category]))

000.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Hello,

 

I have used an altenative solution. 

 

This formula in dax can process only on the table :

 

cumul_obj_max_dax = 
CALCULATE (
 SUM (my_table[OBJ_MAX] );
 ALLEXCEPT ( my_table; my_table[categ_month_year] );
 my_table[obj_date] <= EARLIER ( my_table[obj_date] )
)
I can't load the dax formula in an other table with a link. Due to this situation I have build a SQL view with this :
 
SELECT t1.OBJ_TYPE , t1.OBJ_MAX , t1.OBJ_MIN , t1.obj_categ, t1.obj_date, t1.OBJ_spacetext ,
SUM(t2.obj_max) AS cumul_obj_max 
FROM obj_powerbi t1 
INNER JOIN obj_powerbi t2 ON t2.obj_categ= t1.obj_categAND YEAR(t2.obj_date) = YEAR(t1.obj_date) AND MONTH(t2.obj_date) = MONTH(t1.obj_date) 
WHERE t2.obj_date <= t1.obj_date 
GROUP BY t1.obj_categ, t1.obj_date 
;
My SQL query give me a static result. I can link in an other table. After I can generat others process in a second table/matrix.
 
Thanks
 
 
 
 
Anonymous
Not applicable

Hello @v-diye-msft ,

 

Yes , there is a little error in my example . You have correct this .

 

I'm blocked with you formula :

 

Cumulative sum = CALCULATE(SUM(Table1[Forecast]),FILTER(ALL(Table1),[Date]<=MAX([Date])),VALUES(Table1[Category]))
MIn value of the first day = CALCULATE(MIN([Forecast]),ALLEXCEPT(Table1,Table1[Category]))

You propose that I use a "measure". In my case i look for use a "column" , because in a second step I must link my table with an other.

 

It's possible to use a column and not a measure ?

 

Thanks for your help .

calerof
Impactful Individual
Impactful Individual

Hi @v-diye-msft,

 

How do you make this sum not cumulative, but by each day?

 

Thanks,

 

Fernando

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.