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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Awesome!  This works great and thanks for the reasoning with the code to keep the filter context.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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