Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMWDBA
Advocate II
Advocate II

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
Advocate II
Advocate II

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.