Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 IDNameYearTermSessionY/TTotal Credits End of TermGPA End of TermENROLL_SEPARATION
4Martha2023FALLMAIN2023/FALLNULLNULLENRL
4Martha2023SUMMERSUM12023/SUMMER753.96ENRL
4Martha2023SPRINGMAIN2023/SPRING694ENRL
4Martha2022FALLMAIN2022/FALL634ENRL
4Martha2022SPRINGMAIN2022/SPRING574ENRL
7Terri2023FALLMAIN2023/FALLNULLNULLENRL
7Terri2023SPRINGMAIN2023/SPRING802.9ENRL
7Terri2022FALLMAIN2022/FALL702.94ENRL

 

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 IDNameYearTermSessionY/TTotal Credits End of TermGPA End of TermTotal Credits Start of TermGPA Start of TermENROLL_SEPARATION
4Martha2023FALLMAIN2023/FALLNULLNULL753.96ENRL
4Martha2023SUMMERSUM12023/SUMMER753.96694ENRL
4Martha2023SPRINGMAIN2023/SPRING694634ENRL
4Martha2022FALLMAIN2022/FALL634574ENRL
4Martha2022SPRINGMAIN2022/SPRING574NULLNULLENRL
7Terri2023FALLMAIN2023/FALLNULLNULL802.9ENRL
7Terri2023SPRINGMAIN2023/SPRING802.9702.94ENRL
7Terri2022FALLMAIN2022/FALL702.94NULLNULLENRL

 

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.

1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1697501087196.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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 - __Previous


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 IDNameYearTermSessionY.TY/TCredits for TermTotal Credits End of TermGPA End of TermTotal Credits Start of TermGPA Start of Term
4Martha2023FALLMAIN2023.92023/FALL11NULLNULL  
4Martha2023SUMMERSUM12023.512023/SUMMER6753.96  
4Martha2023SPRINGMAIN2023.12023/SPRING6694  
4Martha2022FALLMAIN2022.92022/FALL6634  
4Martha2022SPRINGMAIN2022.12022/SPRING3574  
7Terri2023FALLMAIN2023.92023/FALL8NULLNULL  
7Terri2023SPRINGMAIN2023.12023/SPRING10802.9  
7Terri2022FALLMAIN2022.92022/FALL9702.94  
7Terri2022SPRINGMAIN2022.12022/SPRING4612.79  
7Terri2021FALLMAIN2021.92021/FALL12572.83  
7Terri2020SPRINGMAIN2020.12020/SPRING6363.2  
7Terri2019FALLMAIN2019.92019/FALL6303.11  
7Terri2019SPRINGMAIN2019.12019/SPRING6243  
7Terri2018FALLMAIN2018.92018/FALL6243  
7Terri2018SPRINGMAIN2018.12018/SPRING6182.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()

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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 IDNameYearTermSessionY.TY/TCredits for TermTotal Credits End of TermGPA End of TermTotal Credits Start of TermGPA Start of Term
4Martha2023FALLMAIN2023.92023/FALL11NULLNULL753.96
4Martha2023SUMMERSUM12023.512023/SUMMER6753.96694
4Martha2023SPRINGMAIN2023.12023/SPRING6694634
4Martha2022FALLMAIN2022.92022/FALL6634574
4Martha2022SPRINGMAIN2022.12022/SPRING3574544
4Martha2021FALLMAIN2021.92021/FALL3544514
4Martha2021SPRINGMAIN2021.12021/SPRING3514  

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1697501087196.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors