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.
Dear experts,
I would like to ask solutions to create a column which summarizes cumulatively value in column "Total" based on column "Date", in 3-day period, for example:
Cumulative sum of "Total" by Date 24/06 = sum of total from date 22/06 to date 24/06
Cumulative sumof Total by Date 23/06 = sum of total from date 21/06 to date 23/06
Date | Id | Total |
20/06/2016 | 11 | 1 |
20/06/2016 | 17 | 2 |
20/06/2016 | 26 | 4 |
21/06/2016 | 179 | 5 |
21/06/2016 | 183 | 7 |
21/06/2016 | 184 | 8 |
21/06/2016 | 298 | 56 |
22/06/2016 | 1025 | 58 |
22/06/2016 | 1028 | 59 |
22/06/2016 | 1030 | 61 |
22/06/2016 | 2624 | 211 |
22/06/2016 | 2682 | 212 |
22/06/2016 | 2752 | 214 |
22/06/2016 | 2772 | 215 |
23/06/2016 | 11 | 217 |
23/06/2016 | 17 | 218 |
23/06/2016 | 28 | 220 |
23/06/2016 | 31 | 221 |
24/06/2016 | 218 | 662 |
24/06/2016 | 220 | 664 |
24/06/2016 | 221 | 665 |
24/06/2016 | 222 | 667 |
24/06/2016 | 224 | 668 |
24/06/2016 | 231 | 670 |
24/06/2016 | 240 | 671 |
24/06/2016 | 243 | 673 |
24/06/2016 | 250 | 674 |
24/06/2016 | 251 | 676 |
24/06/2016 | 264 | 677 |
24/06/2016 | 268 | 679 |
24/06/2016 | 269 | 680 |
24/06/2016 | 276 | 682 |
24/06/2016 | 278 | 683 |
24/06/2016 | 280 | 685 |
24/06/2016 | 293 | 686 |
24/06/2016 | 294 | 688 |
24/06/2016 | 298 | 689 |
24/06/2016 | 299 | 691 |
24/06/2016 | 306 | 692 |
24/06/2016 | 312 | 694 |
24/06/2016 | 313 | 695 |
24/06/2016 | 1013 | 697 |
24/06/2016 | 1018 | 698 |
24/06/2016 | 1021 | 700 |
24/06/2016 | 1023 | 701 |
24/06/2016 | 1028 | 703 |
24/06/2016 | 1030 | 704 |
24/06/2016 | 1031 | 706 |
24/06/2016 | 1032 | 707 |
24/06/2016 | 1033 | 709 |
24/06/2016 | 1050 | 710 |
24/06/2016 | 1053 | 712 |
24/06/2016 | 1055 | 713 |
24/06/2016 | 1059 | 715 |
24/06/2016 | 1068 | 716 |
24/06/2016 | 1074 | 718 |
24/06/2016 | 1076 | 719 |
24/06/2016 | 1082 | 721 |
24/06/2016 | 1083 | 722 |
24/06/2016 | 1102 | 724 |
24/06/2016 | 1106 | 725 |
24/06/2016 | 1107 | 727 |
24/06/2016 | 1110 | 728 |
24/06/2016 | 1141 | 730 |
24/06/2016 | 1143 | 731 |
24/06/2016 | 1145 | 733 |
24/06/2016 | 1151 | 734 |
24/06/2016 | 1152 | 736 |
24/06/2016 | 1153 | 737 |
24/06/2016 | 1154 | 739 |
24/06/2016 | 1156 | 740 |
24/06/2016 | 1158 | 742 |
24/06/2016 | 1159 | 743 |
24/06/2016 | 1162 | 745 |
24/06/2016 | 1163 | 746 |
24/06/2016 | 1166 | 748 |
24/06/2016 | 1167 | 749 |
24/06/2016 | 1170 | 751 |
24/06/2016 | 1179 | 752 |
24/06/2016 | 1180 | 754 |
24/06/2016 | 1184 | 755 |
24/06/2016 | 1185 | 757 |
24/06/2016 | 1186 | 758 |
24/06/2016 | 1190 | 760 |
24/06/2016 | 1193 | 761 |
24/06/2016 | 1199 | 763 |
24/06/2016 | 1200 | 764 |
24/06/2016 | 1201 | 766 |
24/06/2016 | 1204 | 767 |
24/06/2016 | 1212 | 769 |
24/06/2016 | 1220 | 770 |
24/06/2016 | 1227 | 772 |
24/06/2016 | 1229 | 773 |
24/06/2016 | 1238 | 775 |
24/06/2016 | 1245 | 776 |
24/06/2016 | 1246 | 778 |
24/06/2016 | 1260 | 779 |
24/06/2016 | 1261 | 781 |
24/06/2016 | 1262 | 782 |
24/06/2016 | 1264 | 784 |
24/06/2016 | 1266 | 785 |
24/06/2016 | 1271 | 787 |
24/06/2016 | 1272 | 788 |
24/06/2016 | 1273 | 790 |
24/06/2016 | 1274 | 791 |
24/06/2016 | 1276 | 793 |
24/06/2016 | 1287 | 794 |
24/06/2016 | 1288 | 796 |
24/06/2016 | 1291 | 797 |
Thanks in advance for your help!
Solved! Go to Solution.
@KGrice 's solution will work for sure, seems a bit of overkill to have 3 filter clauses for this though. Here's a compressed alternative with the same result:
MovingSum = CALCULATE(SUM(Table2[Total]), FILTER(ALLEXCEPT(Table2, Table2[Id]), Table2[Date] > MAX(Table2[Date])-2 && Table2[Date] <= MAX(Table2[Date])))
Hi @BusinessAnalyst. Try this out:
MovingSum = CALCULATE(SUM(TableName[Total]), FILTER(ALL(TableName), TableName[Date] > MAX(TableName[Date])-2), FILTER(ALL(TableName), TableName[Date] <= MAX(TableName[Date])))
Many thanks for your insight. It worked! I would like to ask if the calculation also based on ID, for example:
Cumulative sum of ID 11 by date 22/06 = total[ID11 in date 20/06] + total[ID11 in date 21/06] + total [ID11 in date 22/06]
(without including other ID like ID17, ID26, etc in date 20/06)
Date | Id | Total | |
20/06/2016 | 11 | 1 | |
20/06/2016 | 17 | 2 | |
20/06/2016 | 26 | 4 | |
21/06/2016 | 179 | 5 | |
21/06/2016 | 11 | 7 | |
21/06/2016 | 184 | 8 | |
21/06/2016 | 298 | 56 | |
22/06/2016 | 1025 | 58 | |
22/06/2016 | 11 | 59 | =1+7+59 |
22/06/2016 | 1030 | 61 | |
22/06/2016 | 2624 | 211 | |
22/06/2016 | 2682 | 212 | |
22/06/2016 | 2752 | 214 | |
22/06/2016 | 2772 | 215 | |
23/06/2016 | 11 | 217 | |
23/06/2016 | 17 | 218 | |
23/06/2016 | 28 | 220 |
I am grateful very much for your help!
Best regards,
Glad to help! You can extend the previous measure to include the Id by adding another filter, like so:
MovingSumByID = CALCULATE(SUM(TableName[Total]),
FILTER(ALL(TableName), TableName[Date] > MAX(TableName[Date])-2),
FILTER(ALL(TableName), TableName[Date] <= MAX(TableName[Date])),
FILTER(ALL(TableName), TableName[Id]=MAX(TableName[Id]))
)
@KGrice 's solution will work for sure, seems a bit of overkill to have 3 filter clauses for this though. Here's a compressed alternative with the same result:
MovingSum = CALCULATE(SUM(Table2[Total]), FILTER(ALLEXCEPT(Table2, Table2[Id]), Table2[Date] > MAX(Table2[Date])-2 && Table2[Date] <= MAX(Table2[Date])))
Dear experts,
I tried both and see that:
@KGrice's solution return to the same value in each row.
And @jahida's solution (MovingSum) doesn't return to desired result.
My wish is as below:
Date | Id | Total | Desired result | Explanation of Result | ||||||
20/06/2016 | 11 | 1 | 1 | sum total {(ID11, date 20/6) + (ID11, date 19/6) + (ID11, date 18/06)}. The result should be = 1 + 0 + 0 = 1, because there is no data of date 19/06 and date 18/06 | ||||||
20/06/2016 | 17 | 2 | 2 | |||||||
20/06/2016 | 26 | 4 | 4 | |||||||
21/06/2016 | 179 | 5 | 5 | |||||||
21/06/2016 | 183 | 7 | 7 | |||||||
21/06/2016 | 11 | 8 | 9 | sum total {(ID11, date 21/6) + (ID11, date 20/6) + (ID11, date 19/06)}. The result should be = 8 + 1 + 0 = 9, because there is no date of date 19/06 | ||||||
21/06/2016 | 298 | 56 | 56 | Result here = 56 = 56 + 0 + 0 because there is only data in date 21/06 (=56) no other date of ID298 in 20/06 and 19/06 | ||||||
22/06/2016 | 1025 | 58 | 58 | |||||||
22/06/2016 | 1028 | 59 | 59 | |||||||
22/06/2016 | 11 | 61 | 70 | sum total {(ID11, date 22/6) + (ID11, date 21/6) + (ID11, date 20/06)}. The result should be = 61 + 8 + 1 = 70 | ||||||
22/06/2016 | 2624 | 211 | 211 | |||||||
22/06/2016 | 2682 | 212 | 212 |
Hope it can be solved! Many thanks to your great contribution!
Cheers!
Hi I Try the Jahida's dax with your sample data and it Works.
Is the same value total and moving sum because your sample data don't have id with 3 consecutive days. I just modified the data with one row for ID 11 in 22/06/16 to test it.
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |