New column with data from the most recent previous term

[Sharo]

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.

[Greg_Deckler]

@Sharo 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/3395....
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 - __Previous

[Sharo]

@Greg_C 

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

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]

Hi,

Do you want the result to be a measure or a calculated column?

[Sharo]

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 · Accepted solution]

Hi,

PBI file attached.

Hope this helps.