Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am moving from Tableau to PowerBI and I am trying to get into the mode of "PowerBI-speak". How would I create the below formulas, which are set for Tableau into language that would work appropriate in PowerBI if I am bringing over the same data tables and field names?
In the data when you look at what is called [Academic Year (Year End Group)] I essential tell it to use the last day of the academic year which would be:
June 30, 2014
June 30, 2015
June 30, 2016
June 30, 2017
Because in the data file the academic year is represented by the first year in the academic year so July 1, 2014 - June 30, 2015 would be labeled as 2014. I grouped them above so that I could put a date that the tableau formula could lookup.
Formula 1: To find Latest Year Headcount
IF DATETIME([Academic Year (Year End Group)])=[Latest Year] THEN [Student ID] ELSE 0 END
Formula 2: To find Prior Year Headcount
IF DATETIME([Academic Year (Year End Group)])=[Prior Year] THEN [Student ID] ELSE 0 END
Formula 3: To find YoY% Change in Student Headcount
SUM([Latest Year Headcount])/SUM([Prior Year Headcount])-1
Thoughts from anyone on how to handle this using the proper DAX formatting?
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.
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
@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.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |