Produce distinct count based on a year to year change condition being met

[jptre8]

Hello all,

Its my first post here and I will start by thanking you all for your contributions. I have used response from this forum so often in the last couple years and I am thankful for the indirect support I have already receined. Now, I need help with a more specific issue that I cant resolve on my own.

I am trying to produce a KPI that essentialy count a number of distinct schools if the number of students has increased in the current year vs the last year.

I can do the year to year difference, but I am unable to used it as a condition for the distinct count.

My main attempt is to use measure #4 below to calculate the number of schools. The results I get is a blank column.

Fiscal Year	Schools with more Students
20192020
20202021

I think it might be linked to the grain difference between the calculation of the diffence and the DISTINCTCOUNT() in measure #4, but I cant find a solution.

Would anyone have an idea that could help me? I am adding the measures and tables below.

Thank you!

Measures:

1. Student Count Last Year = CALCULATE(SUMX('School_Table',[Number of Students]), FILTER(ALLSELECTED('Fiscal_Year_Table'),'Fiscal_Year_Table'[Fiscal_Year _Rank]=max('Fiscal_Year_Table'[Fiscal_Year_Rank])-1))+0
2. Student Count This Year = CALCULATE(SUMX('School_Table',[Number of Students), FILTER(ALLSELECTED('Fiscal_Year_Table'),'Fiscal_Year_Table'[Fiscal_Year_Rank]=MAX('Fiscal_Year_Table'[Fiscal_Year_Rank])))
3. YtYStudent CountDifference = [Student Count Last Year]-[Student Count This Year]
4. Schools with more Students = CALCULATE(DISTINCTCOUNT(School_Table[School_ID]),FILTER([YtYStudent CountDifference]>0))

School_Table

School_ID	Number of Students	Fiscal_Year
1	100	20192020
1	200	20202021
2	200	20192020
2	100	20202021

Fiscal_Year_Table

Fiscal_Year_ID	Fiscal_Year	Fiscal_Year_Rank
1	20192020	1
2	20202021	2
3	20212022	3

[jptre8]

@rajendraongole1 This solution worked like a charm! Thank you for taking the time to help.

[rajendraongole1 · Accepted solution]

Hi @jptre8 - you can adjust the measure for "Schools with more Students" to correctly apply the filter.

Schools with more Students =
CALCULATE(
DISTINCTCOUNT('School_Table'[School_ID]),
FILTER(
VALUES('School_Table'[School_ID]),
[Student Count This Year] > [Student Count Last Year]
)
)

Hope it works.