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
parry2k's avatar
parry2k
Super User
6 years ago

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

parry2k's avatar
parry2k
Super User
6 years ago

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