Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
Who is up to a challenge, that I couldn’t sort out 😞 ?
Scenario:
We are a secondary school and, like any other school, by the end of the year our students:
- Roll up a year (Year 7 become 8, 8 become 9 and so on) <- Spoiler: This is the challenge
- The Year 12 leaves the school
- New enrolments join on the following year
Our data model allow to easily predict new enrolments (showed on attached Matrix)
Desired Outcome:
I want to create a prediction matrix that tells me how many students I will have in 2018, 19, 20 and so on, by getting the (“Number of Current Students” – “Year 12 Students” + “Number of Enrolments”)
For 2018 it would be 886 – 137 + 192 = 941 (Sliced by Year Group, like shown in the Matrix)
Challenge:
Although it is quite simple to have this result for the following year (2018), it isn’t so for the subsequent ones, because in 2019 the students leaving will be our current Year 11 and so on.
How can I sort this issue???
Your assistance is very much appreciated and I would like to thank everyone for your time in advance.
Any chance you can post some sample data. How would you like to come up with a value for each year 7? So for example the unknown future year 7's? or do you have a formula for that?
Is it possible for me to upload the pbix file?
I've cleaned it up, but can't find a way to do so.
You can't upload a PBIX file here, but you can upload to a dropbox, onedrive or googledrive and share a readonly link
Thanks Phill,
Here is the link, hope it helps to get a better understanding
https://drive.google.com/file/d/0BxialLPjHTpbVmlvMk5DUTBfbW8/view?usp=sharing
As a starting point, do you think a measure like this gets close? It's essentially a rolling sum for the previous 6 years, and if close could be tweaked to get right.
Measure = CALCULATE( COUNTROWS('All Students') , FILTER( ALL('All Students'), 'All Students'[Start Year] <= MAX('All Students'[Start Year]) && 'All Students'[Start Year] > MAX('All Students'[Start Year]) - 6 ) )
Hi Phil,
This measure gives me the cumulative figure, but that doesn't do the trick
I thought that might be what you were after. The total number of students in your school on any given year
Hi Phil,
I had a further look and the reason the number is not matching is purely because it is not slicing by EnrollmentStatus.
Anyway to get around that?
Oh definitely. I'll have another look after work tonight. 🙂
Hi Phill, just wondering if you gave this any thoughts.
Thanks in advance
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |