Calculation over a moving window

[heidibb]

Hello,

I have data that looks like the image below (the first 3 columns).

I am trying to creat a calculation that will sum over a rolling 6 terms... see "Rolling" column. Each value in the Rolling column will be a sum for that row + the previous 5 rows, so 6 rows total.

There has to be a way to achieve this with a measure, but I can't seem to figure it out.

Thanks so much!

[parry2k]

@heidibb try following

Rolling 6 rows = 
VAR __currRow = MAX ( Table[TermAxis] )
VAR __startRow = _currRow - 5
RETURN
CALCULATE ( SUM ( Table[Value] ), Table[TermAxis] >= __startRow, Table[TermAxis] <= __currRow)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[heidibb]

Thank you! It's  not quite working.

I created a calculated measure using this per your example:

The output for this is just the same value as the individual term value vs. the sum of the 6 terms

[parry2k]

@heidibb interesting, I just tested at my end and it is working fine, not sure what we are missing here.

[parry2k]

@heidibb do you have any other filters, a relationship which will be causing this, for testing, add ALL ( TableName ) and see if it work, although it is not required.

[heidibb]

Yes, there are other filters. Good catch! All didn't work, but ALLSELECTED did:

THANK YOU!!!

[heidibb]

@parry2k  -- one additional question for you on this cacluation. It's working great, but one additional layer of complexity..  The rolling window for undergraduate terms is 6 terms and the rolling window for graduate terms is 5 terms. I am hoping to have one calculation that does this rolling metric figuring in the different rolling windows. We do have a way to determine the term type of UG and GR, so that can be used as an attribute somewhere.

I thought I could fumble my way through it, but I'm hitting a wall. I am wondering if there is a way to adjust the logic in the __StartRow using the term type criteria?

As a reminder, the current calcuation is:

Rolling Graduated 150% =

VAR __currRow = max ( 'Graduation Retention'[DegreeLevelCohortTermAxis])

VAR __startRow = __currRow - 5

RETURN

CALCULATE ([Total Graduated 150%], ALLSELECTED('Graduation Retention'),'Graduation Retention'[DegreeLevelCohortTermAxis] >= __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <= __currRow)

[Syndicate_Admin]

Just to close the loop on this, I think I've figured it out.

I created a UG fee:

Rolling Graduated 150% UG ?
VAR __currRow máx ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow - __currRow - 5
return
CALCULATE ([Total Graduate 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] - "UG", 'Graduation Retention'[DegreeLevelCohortTermAxis] >-__startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <-__currRow)

And a GR rate:

Rolling Graduate 150% GR ?
VAR __currRow máx ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow - __currRow - 4
return
CALCULATE ([Total Graduate 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] - "GR",'Graduation Retention'[DegreeLevelCohortTermAxis] > __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <-__currRow)

Here's a final measure:

150% - SWITCH(SELECTEDVALUE('Graduation Retention'[Academic Level Code]), "UG", [Rolling Graduated 150% UG] , "GR", [Rolling Graduated 150% GR])

[heidibb]

Just to close the loop on this, I think I've figured it out.

I created a UG rate:

Rolling Graduated 150% UG = 
VAR __currRow = max ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow = __currRow - 5 
RETURN
CALCULATE ([Total Graduated 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] = "UG", 'Graduation Retention'[DegreeLevelCohortTermAxis] >= __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <= __currRow)

And a GR rate:

Rolling Graduated 150% GR = 
VAR __currRow = max ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow = __currRow - 4
RETURN
CALCULATE ([Total Graduated 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] = "GR",'Graduation Retention'[DegreeLevelCohortTermAxis] >= __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <= __currRow) 

Then a final measure:

150% = SWITCH(SELECTEDVALUE('Graduation Retention'[Academic Level Code]), "UG", [Rolling Graduated 150% UG] , "GR", [Rolling Graduated 150% GR])