Forum Discussion

heidibb's avatar
heidibb
Helper IV
7 years ago
Solved

Running Total Over Six Term Timeframe

Hello,

I am trying to build a calculation that will give me a running total over the list six terms as of each term.

My data is set up as follows:

 

 

 

 

 

 

 

 

 

 

What I would like to do is:

For 19AA6, sum my measure for term axis 0 through -5

For 19AA5, sum my measure for term axis -1 through -6

and so on.

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi heidibb ,

     

    You can try to create measure or column like DAX below.

     

    Measure1= 
Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-5))
Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-4))
Return
SWITCH(MAX(Success[Code]),"AA",A6, "BB",B5)
 
 
Column1= 
Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-5))
Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-4))
Return
SWITCH(Success[Code],"AA",A6, "BB",B5)

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    7 years ago

    I think it would be easier/more clear if you used a calculated column instsead:

     

    VAR current = [Axis]
RETURN CALCULATE(SUM(Table), FILTER(Table, [Axis] > current  -6 && [axis] <= current))

     

    • heidibb's avatar
      heidibb
      Helper IV
      7 years ago

      Thank you. Still having a little trouble with this. I used a calculated column with this version of your suggestion:

       

      Rolling = CALCULATE(SUM(Success[TotalEnrollments]), FILTER(Success, [TermAxis] > Success[TermAxis] -6 && [TermAxis] <= Success[TermAxis]))
       
      I'm getting the same value on every row, which is the TOTAL of the "TotalEnrollments" column for the whole table. I just want to total the last term axis rows as of each term axis as I mentioned above. 
       
      What am i missing?
      • artemus's avatar
        artemus
        Microsoft Employee
        7 years ago

        You don't have the VAR. Without it [TermAxis] is relative to the filtering context. The statement as you wrote it is:

        Filter out every row where [TermAxis] is less than or equal to itself and more than itself minus 6.

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi heidibb ,

     

    You can try to create measure or column like DAX below.

     

    Measure1= 
Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-5))
Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=MAX(Success[TermAxis])-4))
Return
SWITCH(MAX(Success[Code]),"AA",A6, "BB",B5)
 
 
Column1= 
Var A6= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-5))
Var B5= CALCULATE(SUMX(Success,[Measure]),FILTER(ALLSELECTED(Success), Success[TermAxis] <=EARLIER(Success[TermAxis])-4))
Return
SWITCH(Success[Code],"AA",A6, "BB",B5)

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  heidibb ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy