Trendline no longer available when going the a lower hierarchy-level

[ZeDe]

Hi,

I have a bar chart with a date hierarchy on the axis and Sales-numbers as the values. When it's showing the default view (years on the axis) I have the possibility to add a trendline, as shown in the picture.

If I click the most right hierarchy-button (to get to a lower level), I get the exact view I want. But as shown in the second picture, there is no option here to show a trendline. 

Am I doing something wrong here or is there a workaround to show a trendline on the second visual?

Also there are 65-ish bars and it's only showing like 50 or so. Is there a way to fix this as well?

Thank you very much

[Anonymous]

Do not use date hierarchy  ,the trend line can be displayed normally by using the year, month and day of the date table.😮

[ZeDe]

Hi @Anonymous ,

I tried linking my date values of my primary table to those of my date table, Added a non-hierarchy Year and Month column. Unfortunately I still don't have the option to select a trendline.

[Anonymous]

you month is not a normal date format.

[ZeDe]

@Anonymous  

These are my DAX-formulas I used to add a calculated column for year and month that i used on my visual:

Year = Year('Calendar'[Date])

Month = Month('Calendar'[Date])

I think that if you want to add a trendline you can only do this when you have one value on your X-axis, and it must be a numerical one (not a categorical).

And as I am using two numerical values (year and month) on my axis, it automatically switches to categorical. The only solution now is the make a bar chart combined with a line chart that displays the trendline through a DAX-formula.

Or am I missing something?

Kind regards

[Anonymous · Accepted solution]

Yours is a good solution , I thought of another one.

Month = Year('Calendar'[Date]) &”-“&Month('Calendar'[Date])

and set it to date format ,Then click here to achieve the desired effect.

[ZeDe]

Hi @Anonymous, I missed the part where i changed the format of my field to date...

Thank you for pointing this out!