Getting previous semester enrolment using DAX measures

[Hoveyg]

Hi Everyone,

I work in the education sector and am looking to setup a PowerBI dashboard that returns the # of students based on enrolment for the previous term of the previous year. ie.  Report ‘Fall 2016 (Term# 1168)’ enrolment alongside ‘Fall 2015(Term# 1158)’ enrolment where I can then look to eventually calculate growth. 

The school terms in an academic year don’t adhere to the traditional fiscal and quarter system so I’m uncertain how to create custom periods. I was able to pull the previous Term#’s enrolment via the following measure but it unfortunately lists the total population from the previous term of the academic year and does not reflect any of the page level pivots/filters/views of the data.

(PowerBI Page Filter on 1168)

PY_StudentCount = CALCULATE([Distinct Students], FILTER(All(CNA_Enrolment), CNA_Enrolment[Term#] = 1158))

**1158 value for Fall 2015 was hardcoded into formula for testing/development.

I receive an output similar to this where the previous year was always the same value no matter how the data was sliced or filtered on the page.

School	Current Enrolment	Previous Year Enrolment	Growth
School of IT	500	5000
School of Business	800	5000
School of Industrial Trades	250	5000

I'd like the previous year enrolment for Fall to pull for each academic school. 

Perhaps it’s not possible to have dynamic calculations based on previous year…. I’ve been reading all about DAX and have a book on order but I’m still struggling to figure out how to get last year's semester enrolment numbers returned in a measure that can be filtered and sliced based on what else was selected in the page.

Any help or suggestions would be appreciated.

Regards,

Greg

[Framet · Accepted solution]

Hi,

The suggested formula is very valid. Just an additional option depending on your setup as the above will return nothing if you have anything in the report alterting the filter context on CNA_Enrolment[Term#] to anything other than 1158.

Additonally you might want to try:

PY_StudentCount = CALCULATE([Distinct Students], FILTER(All(CNA_Enrolment[Term#]), CNA_Enrolment[Term#] = 1158))

You also mention the 1158 was added just for testing, without this what about the formula would return a prior year value?

DAX is certianly able to assist in prior year and indeed many other time intelligence functions and I would agree a good book is really helpful in understanding how get to grips with things.

Thanks

Thomas

[Hoveyg]

Hi Thomas,

Your suggestion did work!!   Thank you very much!!   I guess my next question leads into your questions about 1158.. how do i dynamically assign that value?  I basically want something like CNA_Enrolment[Term#] = CNA_Enrolment[Term#]-10.. ie.   where current term is 1168..  I want to check 1168-10 = 1158 previous period enrolment.

PY_StudentCount = CALCULATE([Distinct Students], FILTER(All(CNA_Enrolment[Term#]), CNA_Enrolment[Term#] = (CNA_Enrolment[Term#]-10)))

Thoughts?  thanks again.

Greg

[Framet]

Hi Greg,

I see your logical thinking and you could approach it this way however I'd probably say for the record it would be bad practice. 

Imagine if your filter context had two terms selected what would CNA_Enrolment[Term#]-10 mean?

A more typical approach would to have a calendar table attached to your main records so you could return anyone enroled between X date and Y date. Alternatively you could also do this by having a semester table with dates that would also work. Once this is setup you can use the time intelligence functions like PARALLELPERIOD and let DAX do the hard work for you.

That all said one option presents its self given what you have described. If you create a calculated column on your main record table that has the formula:

NextTerm#= CNA_Enrolment[Term#]+10

You could use this in a measure that essentially clears the filter on CNA_Enrolment[Term#] and applies it to the new column CNA_Enrolment[NextTerm#]

My best guess at the formula is a little blind as I can't test but consider the below best endeavors. Someone with more experience may be able to be more specific.

PY_StudentCount =
CALCULATE (
    [Distinct Students],
    ALL ( CNA_Enrolment[Term#] ),
    FILTER (
        ALL ( CAN_Enrolment[NextTerm#] ),
        CAN_Enrolment[NextTerm#] = VALUES ( CNA_Enrolment[Term#] )
    )
)

Hope this helps

Thomas

[v-yuezhe-msft]

Hi @Hoveyg，

You use ALL() function in your formula, ALL() function ignores all slicers and Visual/Page/Report filters. Please change your formula to the following formula and check if you get desired result.

PY_StudentCount = CALCULATE([Distinct Students], FILTER(CNA_Enrolment, CNA_Enrolment[Term#] = 1158))

Thanks,
Lydia Zhang

[Hoveyg]

Hi Lydia,

Thank you for your response - is there any way to have a combination of both page filters and ALL() records in a formula?  Ie.  My dashboard is currently filtering for the active term of 1168...  To do a prior period calculation, I need the ALL() to go back and look at term 1158 but there are other page filters and pivots of the data that I have in my dashboard..  I'd need some of those same visual/page/report filters in my dashboard to also filter my previous term enrolment otherwise I won't be comparing apples to apples..

This is an example of my current output using the formula i used..  Removing the ALL() as you suggested returns 0 previous year students as that term we are trying look at does not exist in the current filtered context.

Matrix:

Campus Name -    Current Students     -    Previous Year Students

-----------------------------------------------------------------------

Campus 1         -    500 Students           -    2500

Campus 2         -    1000 Students         -    2500

I would first like the Previous Year students to show only those students for each campus, not the total for the year.  But i'd also eventually like it that if I clicked a chart value or filter on another object in the dashboard, that the previous year students would filter by those value too.. ie.  Click Female half of a pie chart, and my matrix values would update with just the Female numbers.

Matrix: (Filter for Females)  ---IDEAL output

Campus Name -    Current Students     -    Previous Year Students

-----------------------------------------------------------------------

Campus 1         -    256 Students           -    230

Campus 2         -    126 Students           -    145

Perhaps what i'm asking for is not possible..  I keep trying different formulas but with no luck.  Alot of the forums and howtos detail previous periods using regular time period elements using dates which is not exactly applicable to me and my custom period calculation.. Kind of at a loss.

Any additional help would be appreciated.

Thanks,

Greg