cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndrewPNZ Frequent Visitor
Frequent Visitor

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

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. 

image.pngChart 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

2 REPLIES 2
Community Support Team
Community Support Team

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

@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 Frequent Visitor
Frequent Visitor

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

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_YearSnapshot_DateKPI_Value
202010/07/20191
202010/09/201954
202016/09/201967
202018/10/201975
202024/11/201976
202024/12/201998
201907/08/20182
201912/08/201845
201902/02/201956
201904/03/201966
201917/05/201968
201923/10/201979
201808/08/201712
201809/10/201732
201815/10/201754
201803/02/201856
201824/05/201868
201815/08/201889
201818/10/201890
201823/12/2018120

 

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.