cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrewPNZ
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. 

Chart showing trends by year over July <n-1> - December <n>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

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@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.

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.