Forum Discussion
New column with data from the most recent previous term
I have data about students' total credits earned and their GPA, but the data is as of the end of term. For the current term that the student is enrolled in, the two fields are blank. This is what the data looks like.
| Student ID | Name | Year | Term | Session | Y/T | Total Credits End of Term | GPA End of Term | ENROLL_SEPARATION |
| 4 | Martha | 2023 | FALL | MAIN | 2023/FALL | NULL | NULL | ENRL |
| 4 | Martha | 2023 | SUMMER | SUM1 | 2023/SUMMER | 75 | 3.96 | ENRL |
| 4 | Martha | 2023 | SPRING | MAIN | 2023/SPRING | 69 | 4 | ENRL |
| 4 | Martha | 2022 | FALL | MAIN | 2022/FALL | 63 | 4 | ENRL |
| 4 | Martha | 2022 | SPRING | MAIN | 2022/SPRING | 57 | 4 | ENRL |
| 7 | Terri | 2023 | FALL | MAIN | 2023/FALL | NULL | NULL | ENRL |
| 7 | Terri | 2023 | SPRING | MAIN | 2023/SPRING | 80 | 2.9 | ENRL |
| 7 | Terri | 2022 | FALL | MAIN | 2022/FALL | 70 | 2.94 | ENRL |
I would like to create two new columns that populate with the total credits and GPA from the most recent previous term, to give me the Total Credits Start of Term and GPA Start of Term. This is what I am expecting.
| Student ID | Name | Year | Term | Session | Y/T | Total Credits End of Term | GPA End of Term | Total Credits Start of Term | GPA Start of Term | ENROLL_SEPARATION |
| 4 | Martha | 2023 | FALL | MAIN | 2023/FALL | NULL | NULL | 75 | 3.96 | ENRL |
| 4 | Martha | 2023 | SUMMER | SUM1 | 2023/SUMMER | 75 | 3.96 | 69 | 4 | ENRL |
| 4 | Martha | 2023 | SPRING | MAIN | 2023/SPRING | 69 | 4 | 63 | 4 | ENRL |
| 4 | Martha | 2022 | FALL | MAIN | 2022/FALL | 63 | 4 | 57 | 4 | ENRL |
| 4 | Martha | 2022 | SPRING | MAIN | 2022/SPRING | 57 | 4 | NULL | NULL | ENRL |
| 7 | Terri | 2023 | FALL | MAIN | 2023/FALL | NULL | NULL | 80 | 2.9 | ENRL |
| 7 | Terri | 2023 | SPRING | MAIN | 2023/SPRING | 80 | 2.9 | 70 | 2.94 | ENRL |
| 7 | Terri | 2022 | FALL | MAIN | 2022/FALL | 70 | 2.94 | NULL | NULL | ENRL |
Notice that students don't always enroll in the immediate next semester. Therefore, I have to pull the most recent semester before the row semester. With the total credits, I could just pick up the next highest number, because with time the students accumulate more credits. They never lose credits. Unfortunately, the GPA go down as often as up; I can't just take the max GPA.
Hi,
PBI file attached.
Hope this helps.
5 Replies
- Greg_Deckler
Community Champion
Anonymous You will need an actual date or an index column potentially to achieve this. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MINX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousThank you for the blog. I am smarter now than I was when I woke up this morning. However, I think your calculation returns something different from what I need. You are showing how much time passes between the two most recent records for a single student. What I need is to do is identify the most recent previous term, and then return the Total Credits in that previous term.
I created a proxy for time by assigning a decimal that identifies the year and term (=[Y.T]), so that fall 2023 is 2023.9 and summer subsession 1 is 2023.51. Obviously, the summer Y.T is smaller than the fall number, but bigger than the spring number (2023.1).
Student ID Name Year Term Session Y.T Y/T Credits for Term Total Credits End of Term GPA End of Term Total Credits Start of Term GPA Start of Term 4 Martha 2023 FALL MAIN 2023.9 2023/FALL 11 NULL NULL 4 Martha 2023 SUMMER SUM1 2023.51 2023/SUMMER 6 75 3.96 4 Martha 2023 SPRING MAIN 2023.1 2023/SPRING 6 69 4 4 Martha 2022 FALL MAIN 2022.9 2022/FALL 6 63 4 4 Martha 2022 SPRING MAIN 2022.1 2022/SPRING 3 57 4 7 Terri 2023 FALL MAIN 2023.9 2023/FALL 8 NULL NULL 7 Terri 2023 SPRING MAIN 2023.1 2023/SPRING 10 80 2.9 7 Terri 2022 FALL MAIN 2022.9 2022/FALL 9 70 2.94 7 Terri 2022 SPRING MAIN 2022.1 2022/SPRING 4 61 2.79 7 Terri 2021 FALL MAIN 2021.9 2021/FALL 12 57 2.83 7 Terri 2020 SPRING MAIN 2020.1 2020/SPRING 6 36 3.2 7 Terri 2019 FALL MAIN 2019.9 2019/FALL 6 30 3.11 7 Terri 2019 SPRING MAIN 2019.1 2019/SPRING 6 24 3 7 Terri 2018 FALL MAIN 2018.9 2018/FALL 6 24 3 7 Terri 2018 SPRING MAIN 2018.1 2018/SPRING 6 18 2.87 In SSMS, I was very close to getting there using
Select Row_Number() OVER(PARTITION BY [Student ID] ORDER BY [Y.T] desc) as 'Term Sequence'
That numbered all the semesters for each student, so that the most recent was 1 and the first previous was 2, etc. Then I hit a wall. That doesn't create the new columns... I will still have to do some fancy DAX to get the new columns.
Here is my feeble first try at the DAX, based on your blog
Total Credits Start of Term =VAR CurrentTerm = MAXX(FILTER('Academics_AllStudents'[Student ID]),[Y.T])VAR PreviousTerm = MINX(FILTER('Academics_AllStudents','Academics_AllStudents'[Student ID]=EARLIER('Academics_AllStudents'[Student ID]) &&'Academics_AllStudents'[Y.T] < EARLIER('Academics_AllStudents'[Y.T])),[Y.T])RETURN IF()- Ashish_Mathur
Super User
Hi,
Do you want the result to be a measure or a calculated column?
