Forum Discussion

heidibb's avatar
heidibb
Helper IV
6 years ago

Calculation over a moving window

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 ...
Need Help
heidibb's avatar
heidibb
Helper IV
6 years ago

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's avatar
Syndicate_Admin
Administrator
5 years ago

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])