Ideas on how to create a chart comparing a metric over date ranges Jul. <n>-1 to Dec. <n>

[AndrewPNZ]

Our management team tracks our enrolment metrics for a given year from July the previous year, and would like to see this on a graph. To do this, I currently have a new date column with: IF the enrolment is for the next year, get the day of year it was created in, and create a new date with <day of year>, 1900. IF the enrolment is for the current year, create a new date with <day of year>, 1901.

This gives the below graph, where the X axis is between July 1900 and December 1901. That doesn't make sense to users, so I've hidden that axis and added a label with the below fake x-axis label. 

Chart showing trends by year over July <n-1> - December <n>

This works well on the surface. A custom report tooltip page is used to hide the 1900/1901 dates from the user.

However I'm worried this is creating slight inaccuracies - day 123 of 2016 could be 1st of July (start of a known enrolment-increasing event), but day 123 of 2017 could be 3rd July. By setting these both as the same date in 1900, we're creating information that I don't think stacks up under scrutiny where the specific date of a change in KPI trajectory matters.

Is there a better way to do a graph that shows the "July <n-1> - Dec. <n>" over years than the above use of fake dates in 1900/1901?

Best,

Andrew

[v-yuta-msft]

@AndrewPNZ ,

Could you share some sample data and clarify more details about your requirement?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndrewPNZ]

Hi @v-yuta-msft, I've uploaded a sample Power BI to Google Drive. 

Trying to clarify the requirement: I have a table of snapshot values, each tied to a specific focus (KPI) year. These values range from 1st July one year to December 31st the next. I am trying to chart these values as a continuous line for each focus/KPI year. I currently get the month and day, or week of year, and translate that value to between 1st July 1900 to 31st December 1901. 

This makes anywhere the x-axis labels show show 1900-1901. I want to know if the way I am doing this is appropriate, or if there is a better method of achieving the same result?

Sample snapshot value data, showing dates the snapshot were taken on from 1st July - December 31st, and the KPI year each snapshot value relates to.

KPI_Year	Snapshot_Date	KPI_Value
2020	10/07/2019	1
2020	10/09/2019	54
2020	16/09/2019	67
2020	18/10/2019	75
2020	24/11/2019	76
2020	24/12/2019	98
2019	07/08/2018	2
2019	12/08/2018	45
2019	02/02/2019	56
2019	04/03/2019	66
2019	17/05/2019	68
2019	23/10/2019	79
2018	08/08/2017	12
2018	09/10/2017	32
2018	15/10/2017	54
2018	03/02/2018	56
2018	24/05/2018	68
2018	15/08/2018	89
2018	18/10/2018	90
2018	23/12/2018	120

The end goal: we monitor KPI trends for a given year from July the previous year. We want to show the KPI trend from July Previous Year to December of the relevant year as a continuous line on a chart. We want to show this as a separate line/series for each KPI year to see how the KPI for the current or next year is trending compared to other years.