Running Total Over Six Term Timeframe

[heidibb]

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.

[v-xicai]

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

[v-xicai · Accepted solution]

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]

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]

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]

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]

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]

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