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 ...
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
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.

    • heidibb's avatar
      heidibb
      Helper IV
      7 years ago

      GOT IT! 

      Thank you! I have one more level of complexity.


      Is there a way to add an add'l filter to this? For example:

      Periods that are "AA" i want to sum the last 6

      Periods that are "BB" i want to sum the last 5

       

      I do have a field in my data for period code (AA or BB)

       

      I've got it for the AA, but not sure how to add in the BB to the same calc:

       

      Rolling =
      VAR abc = Success[TermAxis]
      RETURN CALCULATE(SUM(Success[Total Enrollments]), FILTER(Success, Success[Code] = "AA" && [TermAxis] > abc -6 && [TermAxis] <= abc))
       
      Can i add to this to account for the BB code so it sums only the last 5?
       
      Thanks for all your help!
      • artemus's avatar
        artemus
        Microsoft Employee
        7 years ago

        Sure, just use:

        IF(Success[Code] = "AA",<true logic>, <false logic>)

         

        <false logic> can also include an IF

         

        If you have a lot of these use a SWITCH