Forum Discussion
Getting previous semester enrolment using DAX measures
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
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
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
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