cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jocy
Frequent Visitor

Enrollment Renovation: Identify students with enrollment in previous year

Hi all,

I'm wondering if i can get help with this, I'm working with a school's database and I have the "Enrollment Table" with:

  • Student ID
  • Enrollment Year (2016, 2017, 2018, etc)
  • Cursus ID

That means that every year, each student got a new register in the database, identifying the year and the actual cursus assigned.

 

And I need to calculate how many of the actual enrolled students where enrolled from past year, my biggest issue is that the formula must work for any selected year.

 

Please if someone can help me! Thx

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @jocy,

 

Try this measure:

 

Enrollment previous year =
VAR select_year =
    MAX ( 'Enrollment Table'[Enrollment Year] )
VAR students =
    VALUES ( 'Enrollment Table'[Student ID] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                ALL ( 'Enrollment Table'[Student ID]; 'Enrollment Table'[Enrollment Year] );
                'Enrollment Table'[Enrollment Year];
                'Enrollment Table'[Student ID]
            );
            'Enrollment Table'[Enrollment Year]
                = select_year - 1
                && 'Enrollment Table'[Student ID] IN students
        )
    )

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
jocy
Frequent Visitor

Hi all,

I'm wondering if i can get help with this, I'm working with a school's database and I have the "Enrollment Table" with:

  • Student ID
  • Enrollment Year (2016, 2017, 2018, etc)
  • Cursus ID

That means that every year, each student got a new register in the database, identifying the year and the actual cursus assigned.

 

And I need to calculate how many of the actual enrolled students where enrolled from past year, my biggest issue is that the formula must work for any selected year.

 

Please if someone can help me! Thx

MFelix
Super User III
Super User III

Hi @jocy,

 

Try this measure:

 

Enrollment previous year =
VAR select_year =
    MAX ( 'Enrollment Table'[Enrollment Year] )
VAR students =
    VALUES ( 'Enrollment Table'[Student ID] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                ALL ( 'Enrollment Table'[Student ID]; 'Enrollment Table'[Enrollment Year] );
                'Enrollment Table'[Enrollment Year];
                'Enrollment Table'[Student ID]
            );
            'Enrollment Table'[Enrollment Year]
                = select_year - 1
                && 'Enrollment Table'[Student ID] IN students
        )
    )

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

MFelix
Super User III
Super User III

Hi @jocy,

 

Believe that this post is repeated with:

 

https://community.powerbi.com/t5/Desktop/Enrollment-Renovation-Rate/m-p/474635#M220706

 

Regards,

Mfelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jocy
Frequent Visitor

@MFelix Thanks for your help, is exactly what I was looking for and your answer was very fast.

 

I had a couple issues last days when posting on the forum, so I couldn't fix my repeated post or thank you sooner!.

 

Thx again!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors