Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Helper I
10 years ago
Solved

cumulative running sum

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

 

DateIdTotal
20/06/2016111
20/06/2016172
20/06/2016264
21/06/20161795
21/06/20161837
21/06/20161848
21/06/201629856
22/06/2016102558
22/06/2016102859
22/06/2016103061
22/06/20162624211
22/06/20162682212
22/06/20162752214
22/06/20162772215
23/06/201611217
23/06/201617218
23/06/201628220
23/06/201631221
24/06/2016218662
24/06/2016220664
24/06/2016221665
24/06/2016222667
24/06/2016224668
24/06/2016231670
24/06/2016240671
24/06/2016243673
24/06/2016250674
24/06/2016251676
24/06/2016264677
24/06/2016268679
24/06/2016269680
24/06/2016276682
24/06/2016278683
24/06/2016280685
24/06/2016293686
24/06/2016294688
24/06/2016298689
24/06/2016299691
24/06/2016306692
24/06/2016312694
24/06/2016313695
24/06/20161013697
24/06/20161018698
24/06/20161021700
24/06/20161023701
24/06/20161028703
24/06/20161030704
24/06/20161031706
24/06/20161032707
24/06/20161033709
24/06/20161050710
24/06/20161053712
24/06/20161055713
24/06/20161059715
24/06/20161068716
24/06/20161074718
24/06/20161076719
24/06/20161082721
24/06/20161083722
24/06/20161102724
24/06/20161106725
24/06/20161107727
24/06/20161110728
24/06/20161141730
24/06/20161143731
24/06/20161145733
24/06/20161151734
24/06/20161152736
24/06/20161153737
24/06/20161154739
24/06/20161156740
24/06/20161158742
24/06/20161159743
24/06/20161162745
24/06/20161163746
24/06/20161166748
24/06/20161167749
24/06/20161170751
24/06/20161179752
24/06/20161180754
24/06/20161184755
24/06/20161185757
24/06/20161186758
24/06/20161190760
24/06/20161193761
24/06/20161199763
24/06/20161200764
24/06/20161201766
24/06/20161204767
24/06/20161212769
24/06/20161220770
24/06/20161227772
24/06/20161229773
24/06/20161238775
24/06/20161245776
24/06/20161246778
24/06/20161260779
24/06/20161261781
24/06/20161262782
24/06/20161264784
24/06/20161266785
24/06/20161271787
24/06/20161272788
24/06/20161273790
24/06/20161274791
24/06/20161276793
24/06/20161287794
24/06/20161288796
24/06/20161291797


Thanks in advance for your help! 

  • jahida's avatar
    jahida
    10 years ago

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

6 Replies

  • KGrice's avatar
    KGrice
    Memorable Member
    10 years ago

    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's avatar
      BusinessAnalyst
      Helper I
      10 years ago

      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)

       

      DateIdTotal 
      20/06/2016111 
      20/06/2016172 
      20/06/2016264 
      21/06/20161795 
      21/06/2016117 
      21/06/20161848 
      21/06/201629856 
      22/06/2016102558 
      22/06/20161159 =1+7+59
      22/06/2016103061 
      22/06/20162624211 
      22/06/20162682212 
      22/06/20162752214 
      22/06/20162772215 
      23/06/201611217 
      23/06/201617218 
      23/06/201628220 

       

      I am grateful very much for your help!

       

      Best regards,

      • KGrice's avatar
        KGrice
        Memorable Member
        10 years ago

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