Forum Discussion
Time series with proxy label
I'm working on some time series data that uses a PREVIOUSYEAR function for YoY change at the year level. Keeping the year as a date type lets me do the previous year calculation but the x-axis label is obviously the year. What I'd like to be able to do is keep the results as is but show the academic year on the x axis instead of the year label. So 2016 would be ACAD16/17 and 2017 ACAD17/18 etc. I don't need to worry about the year start/end as it's just a single date I'm looking at in each year (entry date of students).
Does anyone know of a way that lets me display a proxy value on the axis label or a way of working with ACAD16 while keeping the structure attributed by a date format?
As you are working on PREVIOUSYEAR, so supposing you already have a good Date table, you just need to add a calculated column in your Date table, the code is like this:
ACAD =
"ACAD"&YEAR(Date[Date])&"/"&(YEAR(Date[Date])+1)
Then put the Date[ACAD] to your X axis.
4 Replies
Try this example for fiscal year and adapt it for accademic year ...
Click new table and
Calendar = CALENDAR(DATE(2020,01,01),DATE(2023,12,31))Add new column ...Fiscal year =// the uk fiscal years commences on 6th AprilVAR fiscalstartmonth = 05VAR fiscalstartday = 06RETURNSWITCH( TRUE(),MONTH('Calendar'[Date]) < fiscalstartmonth, YEAR('Calendar'[Date]) - 1,MONTH('Calendar'[Date]) > fiscalstartmonth, YEAR('Calendar'[Date]),DAY('Calendar'[Date]) < fiscalstartday, YEAR('Calendar'[Date]) - 1,YEAR('Calendar'[Date]))Add new column ...Fiscal year offset =// the uk fiscal years commences on 6th AprilVAR fiscalstartmonth = 05VAR fiscalstartday = 06VAR todayyear = YEAR(TODAY())VAR todaymonth = MONTH(TODAY())VAR todayday = DAY(TODAY())VAR todayfiscalyear =SWITCH( TRUE(),todaymonth < fiscalstartmonth, todayyear - 1,todaymonth > fiscalstartmonth, todayyear ,todayday < fiscalstartday, todayyear - 1,todayyear)RETURN'Calendar'[Fiscal year] - todayfiscalyearAdd 1:M relationship from calendar date to uour fact table date.
Add news measures ..
Sales = SUM(tablename[amount])Sales this fiscal year = CALCULATE( Sales, 'Calenadr'[Fiscal year offset] = 0)
Sales previous fiscal year = CALCULATE( Sales, 'Calenadr'[Fiscal year offset] = -1)
Please do all these free power BI calendar traing courses, escepcually the one about offsets
All Power BI reports use dates, so this training is really important.
Thanks for reaching out for help.
I put in a lot of effort to help you, now please quickly help me by giving kudos.
Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
As you are working on PREVIOUSYEAR, so supposing you already have a good Date table, you just need to add a calculated column in your Date table, the code is like this:
ACAD =
"ACAD"&YEAR(Date[Date])&"/"&(YEAR(Date[Date])+1)
Then put the Date[ACAD] to your X axis.Thanks Freeman. This worked a treat!
for anyone else looking to do similar. If you want the time series to be in order you'll also need to sort your date table by the ACAD year column. similar to this
https://community.powerbi.com/t5/Desktop/Month-Name-in-sorting-order/m-p/152006
