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

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

 

 

THANK YOU!!!

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)
  • heidibb's avatar
    heidibb
    Helper IV
    6 years ago

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