The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.
User | Count |
---|---|
137 | |
108 | |
80 | |
69 | |
59 |
User | Count |
---|---|
258 | |
128 | |
116 | |
100 | |
80 |