Forum Discussion
YoY Change in Registrations
For PowerBI, are you wanting to have these formulas exist in columns you add to the data using these formulas?
Without seeing the data you are using, i tried to recreate something myself, so it may not be correct. It seems like most of the issues are just different syntax.
By adding each formula as one column per formula, I got:
Formula 1:
Latest Year = if('Academic Year'[Year End Group]=max('Academic Year'[Year End Group]), 1, 0)
Formula 2:
Prior Year = if('Academic Year'[Year End Group]=max('Academic Year'[Year End Group])-1, 1, 0)
Formula 3:
YoY% Change in Student Headcount = sum('Academic Year'[Latest Year])/SUM('Academic Year'[Prior Year])-1
I used max('Academic Year'[Year End Group]) because I could not tell where you were getting [Latest Year] and [Prior Year] from.
I didn't display the student ID when formula 1 or 2 evaluated to tru because summing student IDs could give you strange totals, so instead we are summing 1's.
I wonder if I can try to create a dummy file so that I am not breaking university rules in sharing data. Its hard to explain so I think it is something you have to see. I can follow the DAX Flow. Let me try to do something with at least 5 lines of dummy information so its not breaking any rules.
A dummy file are better than any explanation. You can upload a dummy pbix in a network driver such as Onedrive, Google drive and share a link.
See the link to the dummy file. (Never realized creating a dummy file was so complex). So as you will see from the first column we identify academic year as "YYYY-YY" i.e. 2014-15. The beginning of that year would be July 1, 2014 and the end would be June 30, 2015. Academic years always start on July 1 and end on June 30. Since its not a date itself that's why I picked the last day of the academic year just to give it a date.
All field headers are true.
So I want to
- Look at headcount by Academic Year (easiest to figure out)
- Compare the YoY% Change by Academic Year and by Academic Plan
- Find the average across academic years and academic plan
- Create a KPI that shows whether headcount for the current AY (which would be 2016-17) is up or down compared to previous
JMWDBA wrote:
See the link to the dummy file. (Never realized creating a dummy file was so complex). So as you will see from the first column we identify academic year as "YYYY-YY" i.e. 2014-15. The beginning of that year would be July 1, 2014 and the end would be June 30, 2015. Academic years always start on July 1 and end on June 30. Since its not a date itself that's why I picked the last day of the academic year just to give it a date.
All field headers are true.
So I want to
- Look at headcount by Academic Year (easiest to figure out)
- Compare the YoY% Change by Academic Year and by Academic Plan
- Find the average across academic years and academic plan
- Create a KPI that shows whether headcount for the current AY (which would be 2016-17) is up or down compared to previous
Thanks for uploading the dummy data. I change the "2014-15" to a valid date "2014-07-01" and create a calendar table. In the calendar table, mark the Academic Year and create one to many relationship to the Academic table.
Check more details in the attached pbix. By the way, I don't get the 3rd requirement, so if you can't figure it out, please post more explanation.
