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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndersonLegnar
Frequent Visitor

Predictive Calculations

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)

 image.png

 

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.

10 REPLIES 10
Phil_Seamark
Employee
Employee

Hi @AndersonLegnar

 

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Is it possible for me to upload the pbix file?

I've cleaned it up, but can't find a way to do so.

 

 

Hi @AndersonLegnar

 

You can't upload a PBIX file here, but you can upload to a dropbox, onedrive or googledrive and share a readonly link


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks Phill,

Here is the link, hope it helps to get a better understanding

https://drive.google.com/file/d/0BxialLPjHTpbVmlvMk5DUTBfbW8/view?usp=sharing

HI @AndersonLegnar

 

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
                    )
                    )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

This measure gives me the cumulative figure, but that doesn't do the trickss.png

 

I thought that might be what you were after.  The total number of students in your school on any given year


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phill, just wondering if you gave this any thoughts.

Thanks in advance

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.