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
EpicTriffid
Helper IV
Helper IV

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
Resident Rockstar
Resident Rockstar

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.

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 😕

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

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.