Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |