Enrollment Renovation: Identify students with enrollment in previous year

[jocy]

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

[jocy]

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

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

[MFelix]

Hi @jocy,

Believe that this post is repeated with:

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

Regards,

Mfelix

[jocy]

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