cumulative running sum

[BusinessAnalyst]

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! 

[KGrice]

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])))

[BusinessAnalyst]

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,

[KGrice]

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]))
	)

[jahida · Accepted 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])))

[BusinessAnalyst]

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!

[Vvelarde]

@BusinessAnalyst

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.