cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EpicTriffid Regular Visitor
Regular Visitor

Dealing with Academic Years

Hi all, 

 

So I'm currently trying to create a KPI report and I'm encountering some difficulty with academic year (e.g. 2017/18, 2018/19). This is how we report all our data as the academic year straddles the calendar year. This hasn't been problem up until I just tried to start mapping year on year change. In order to get that to work I've created a new column (YearDate) formatted as a date with which i can use PARALLELPERIOD to get the year on year change in a measure (Diff)

You can see my first mock-up below:

 

Capture.PNG

This works, getting the previous years data. The Diff column is built off a "Last Year" column that finds the previous years values:

 

LY = CALCULATE([SwitchScores], PARALLELPERIOD(KPIs_COMBINED_RESHAPED[YearDate].[Date],-1,YEAR))

However, when I change the table to show the Academic Year instead, I come up against zeroes:

 

Capture1.PNG

 

I was under the impression that because the formatted YearDate column was alongside the Acdemic year, PBI would find the connection and shoiw me the values even if the column said something different. I can't seem to get my head around why this isn't working.

 

Essentially, I need the years to look like 2018/19 instead of just 2018, and I can't figure out how without also losing the functionality of parallelperiod.

 

Cheers

3 REPLIES 3
sturlaws New Contributor
New Contributor

Re: Dealing with Academic Years

Hi @EpicTriffid ,

do you have a separate date table? With a separate date table, with a relationship on "normal" date, you can have second column in the date table with [Academic year]. I think this will give you the behaviour you are looking for

 

Cheers,
Sturla


If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

EpicTriffid Regular Visitor
Regular Visitor

Re: Dealing with Academic Years

Hi @sturlaws 

 

Thanks for the response. I tried what you suggested but no luck. With a date table, and a relationship between the normally formatted dates in both, it still doesn't work. Here it is with the formatted date:

 

Capture.PNG

 

And with the "Academic Year" (called Actual Year here):

 

Capture1.PNG

 

Im a little lost as to why it's not working 😕

sturlaws New Contributor
New Contributor

Re: Dealing with Academic Years

I created a sample report on how you can do it: pbix 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)