Forum Discussion
New column with data from the most recent previous term
Hi,
PBI file attached.
Hope this helps.
Thank 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
Super UserHi,
Do you want the result to be a measure or a calculated column?
It should be a measure. If I want to see the GPA of all students enrolled in the current semester, I will see nothing but nulls, because the database only holds end of semester GPA. I want to fish the GPA from the students previous semester (whenever that might be) and put it in the record with the current semester. It doesn't need to be calculated, because it is all fixed historical data.
The red columns are what I want. You can see that they are from the two previous columns, one row down. Y.T (in blue) marks the year and month of the semester.
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 75 3.96 4 Martha 2023 SUMMER SUM1 2023.51 2023/SUMMER 6 75 3.96 69 4 4 Martha 2023 SPRING MAIN 2023.1 2023/SPRING 6 69 4 63 4 4 Martha 2022 FALL MAIN 2022.9 2022/FALL 6 63 4 57 4 4 Martha 2022 SPRING MAIN 2022.1 2022/SPRING 3 57 4 54 4 4 Martha 2021 FALL MAIN 2021.9 2021/FALL 3 54 4 51 4 4 Martha 2021 SPRING MAIN 2021.1 2021/SPRING 3 51 4 - Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.