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.
I want to have a column chart that displays Sales Amount by a date hierarchy (year, month, quarter) and also displays trend lines for each level of the hierarchy.
I have something that is close to working but I have to use numeric values for the year, month, and quarter and this is causing the x-axis labels to be their numeric form instead of an easy to read text form (for example quarter "8070" is meaningless but quarter "Q2-2017" is easily understood).
Here is my PBIX file that I'm using, https://www.dropbox.com/scl/fi/6afmiykjhpar3seoop865/C0223-Implementing-linear-regression-in-Power-B...
It is a modified version of the file from the SQLBI article (https://www.sqlbi.com/articles/implementing-linear-regression-in-power-bi/).
I'll try to use images below to convey what my issue is and what I'm looking for.
The images below have two column charts along with two field parameter slicers. The top set of visuals is using the numeric date values (Year, Year Quarter Number, and Year Month Number) and the bottom set of visuals is using the text date values (Year, Year Quarter, and Year Month). The dotted yellow line in each chart is the default trend line. I enabled it to see if it matched the trend line from the DAX measure and it does.
In this first image, I select "Year" and "Year" for each field parameter. Everything is working fine for the year because the Year column is in numeric form.
Now, I select the "Year Quarter Number" and "Year Quarter" for each field parameter. The top chart has the correct shape but the x-axis is not what I want. I want the x-axis from the bottom chart but the bottom chart does not display the correct trend.
Now, I select the "Year Month Number" and "Year Month" for each field parameter. I experience the same issue where the top chart is correct but the x-axis labels are not what I want.
How can I have the top chart shape but have the x-axis labels be from the bottom chart?
I'm not even sure if my approach is the right one. If anyone has any ideas for how to implement a column chart that displays trend lines for multiple different levels of dates that would be greatly appreciated!
Solved! Go to Solution.
Hi @Drew89
Appreciate your effort to post a good and well explained question.
The below file has working solution, All I did is add [year Quarter] in Var known summarize table as below[did the same thing for month trend as well]. This is required to keep the filter context intact.
Also note that the Trend line from analytics pane can't be leveraged if the x axis is a string.
Let me know if this helps.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!
Hi @Drew89
Appreciate your effort to post a good and well explained question.
The below file has working solution, All I did is add [year Quarter] in Var known summarize table as below[did the same thing for month trend as well]. This is required to keep the filter context intact.
Also note that the Trend line from analytics pane can't be leveraged if the x axis is a string.
Let me know if this helps.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!
Wow, great work! Just a note for others: If you're struggling to calculate the year-month number because you're using a concatenated year and month, which causes drops between years, here’s the method:
Year Month Number = YEAR([Date]) * 12 + MONTH([Date])
You need only set-up sorting your string date value based on this caculated value.
Awesome! This works great and thanks for the reasoning with the code to keep the filter context.
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 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |