Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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]))
Best regards,
Dina Ye
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 .
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]))
pbix attached here for your reference:
Best regards,
Dina Ye
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]))
Best regards,
Dina Ye
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] ) )
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.
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 .
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |