cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sander-fr Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative sum by day and category

Hi @sander-fr ,

 

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.
sander-fr Regular Visitor
Regular Visitor

Re: Cumulative sum by day and category

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 .

5 REPLIES 5
Community Support Team
Community Support Team

Re: Cumulative sum by day and category

Hi @sander-fr ,

 

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

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.
sander-fr Regular Visitor
Regular Visitor

Re: Cumulative sum by day and category

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.

Community Support Team
Community Support Team

Re: Cumulative sum by day and category

Hi @sander-fr ,

 

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.
sander-fr Regular Visitor
Regular Visitor

Re: Cumulative sum by day and category

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 .

Highlighted
sander-fr Regular Visitor
Regular Visitor

Re: Cumulative sum by day and category

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
 
 
 
 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 303 members 2,954 guests
Please welcome our newest community members: