Forum Discussion

heidibb's avatar
heidibb
Icon for Helper IV rankHelper 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 ...
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.

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft 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
Icon for Helper IV rankHelper 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?