Forum Discussion

Hoveyg's avatar
Hoveyg
Regular Visitor
9 years ago
Solved

Getting previous semester enrolment using DAX measures

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 ‘...
Need Help
  • Framet's avatar
    Framet
    9 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Hoveyg
Regular Visitor
9 years ago

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