Sales per week - no trend line when showing multiple years

[PeterStuhr]

Hi all,

I have trouble getting a trendline when using Year and Weeknumbers.

It will only let me select "Continous" if I only use Weeknumbers. But if I then select 2020 and 2021, it will combine week 1 2020 with week 1 2021 etc.

So I have to use Year and Weeknumbers in X axis. But then it wont let me make it Continous to get a trendline.

if someone can make it work to show multiple years with weeks, and a trendline - please send me the file!

THanks!

[Cyndiloo_92]

Following Up.... Did anyone ever figure out a "work around" for adding a trendline categorically? 

[v-yuaj-msft]

Hi @PeterStuhr ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[v-yuaj-msft · Accepted solution]

Hi @PeterStuhr ,

I think that because both fields are in the "axis", the horizontal axis is not a continuous variable, so the trend line function cannot be used.

As I mentioned before, unfortunately, Power BI currently cannot create trend lines based on categorical variables. You can use a R or Python visual instead. Hope these effects will be added soon.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yuaj-msft]

Hi @PeterStuhr ,

Based on your description, I did a test. From the test results, the line chart only provides a trend line when the x-axis variable is a continuous variable. You can create a simple calculated column as follows.

Column = YEAR([Date])*100+[week]

My test table:

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PeterStuhr]

Hi @v-yuaj-msft 

Thanks, the problem with this is that it will just create a huge gab between 202053 and 202101, as shown in your 😕

So I think we have a big limitation here... Ideally, I would like something like this:

I can add an "Average" line, but it really annoys me that I can not make a trendline!

[PeterStuhr]

I still dont seem to be able to select "trendline" in the analysis pane?

[amitchandak]

@PeterStuhr , Not very clear. You can take year and week on axis and sort on that and switch off concatenate label off