Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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
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:
And with the "Academic Year" (called Actual Year here):
Im a little lost as to why it's not working 😕
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |