Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
Drew89
Frequent Visitor

How to get trend line for every level in date hierarchy?

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.

 

Drew89_0-1720629798243.png

 

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. 

 

Drew89_1-1720629912199.png

 

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.  

 

Drew89_2-1720630594414.png

 

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!

 

1 ACCEPTED SOLUTION
NaveenGandhi
Super User
Super User

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. 

NaveenGandhi_0-1720641675212.png

NaveenGandhi_1-1720641734604.png

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!!

View solution in original post

3 REPLIES 3
NaveenGandhi
Super User
Super User

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. 

NaveenGandhi_0-1720641675212.png

NaveenGandhi_1-1720641734604.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.