Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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
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_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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |