cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMWDBA
Helper III
Helper III

YoY Change in Registrations

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

 

6 REPLIES 6
JMWDBA
Helper III
Helper III

Thoughts from anyone on how to handle this using the proper DAX formatting?

Trotzuk
Advocate I
Advocate I

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. 

 

Link: https://myerauedu-my.sharepoint.com/personal/willij42_erau_edu/_layouts/15/guestaccess.aspx?docid=1e...

 

So I want to

  1. Look at headcount by Academic Year (easiest to figure out)
  2. Compare the YoY% Change by Academic Year and by Academic Plan
  3. Find the average across academic years and academic plan
  4. 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. 

 

Link: https://myerauedu-my.sharepoint.com/personal/willij42_erau_edu/_layouts/15/guestaccess.aspx?docid=1e...

 

So I want to

  1. Look at headcount by Academic Year (easiest to figure out)
  2. Compare the YoY% Change by Academic Year and by Academic Plan
  3. Find the average across academic years and academic plan
  4. Create a KPI that shows whether headcount for the current AY (which would be 2016-17) is up or down compared to previous

@JMWDBA

 

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.

 

Capture3.PNG

 

Capture.PNGCapture2.PNG

 

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. 

@JMWDBA

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.