Linear regression - Challenges with trend line at year change

[Anonymous]

Hi all,

In power Bi desktop, I have been using a dax expression to model the trend line of production output per week (bellow).
It was working just fine, before the year change.
However, when including weeks from different years (in this case, weeks 52 and 1, from year 2021 and 2022 respectively) my trend line has split into three diferent slopes, like so:

One slope for the 2021's weeks until 52 (decreasing), another slope for between 52 and 1 (increasing), and another for selected weeks in 2022 (decreasing again, different slope).

My goal is to have the trend line show up as a continuum, with one slope only, to model the complete selected period, regardless of year.

The measure I'm using for trend line is the following:

Trend line =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( DateTable[Week] ),
"Known[X]", DateTable[Week],
"Known[Y]", CALCULATE ( SUM ( ProductionTable[Output] ) )
),
AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X * Sum_X
)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
SUMX ( DISTINCT ( DateTable[Week] ), Intercept + Slope * DateTable[Week] )

Other important details:

ProductionTable[Output] is measured per day,

ProductionTable has it's own [Output_Date] column (that is the date when output was measured).

ProductionTable is connected to DateTable through a many to one relationship using columns ProductionTable[Output_Date] and DateTable[Date]. Cross filter is single.

DateTable contains a 'Date Hierarchy', of which Week is not a part of.

'Date Hierarchy' contains Year, Quarter, Month,Day.

The visualization should show Trendline for output, per week of the year. These are the fiels in my axis':

My DateTable has the following columns/data:

Any tips on what I can do to make the trend line have one slope for the whole selected period, regardless of year?

Thanks for any help,

I can provide more info if you have questions

[johnt75]

Instead of having both year and week on the axis use the [Year - Week] column from your date table, and change the trend line measure to use that column instead of the week column.

You may need to create another column in the date table for sorting the Year - Week by.

[Anonymous]

Hi johnt75, thanks for your input.

I did what you suggested.

Changed the X-Axis to Year-Week instead (which has datatype string)

It now looks like so:

Have also sorted it by WeekofyearKey, which has data type wholenumber:

Finally, have changed the measure for trendline to use Year-Week, and called it Trend line 2:

Trend line 2=
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( DateTable[Year - Week] ),
"Known[X]", DateTable[Year - Week],
"Known[Y]", CALCULATE ( SUM ( ProductionTable[Output] ) )
),
AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
)
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X * Sum_X
)
VAR Intercept = Average_Y - Slope * Average_X
RETURN
SUMX ( DISTINCT ( DateTable[Year - Week] ), Intercept + Slope * DateTable[Year - Week] )

Now measures Trend line and Trend line 2 present different behaviours in the visualization:

Trendline is now overlapping values of ProductionTable[Output] , like so:

As for Trend line 2 

 It generates an error message saying in ProductionTable[Trend line 2] : The function SUMX cannot work with values of type string

So the problem seems to be, in order to create the trend line through linear regression, The X axys needs to be either a date or a whole number. Strings don't work for that purpose.

How do I achieve Year -week as a date or whole number?

Or, if the problem is just function SUMX, are there any alternatives to it that can use strings?

I have tried something else, that also didn't work :

• Changed the X-axis to WeekofYearKey

As WeekofYearKey has datatype whole number (it is a justaposition of year and week with the format YYYYWW) I thought I could give it a try and see how it would look like if I used it as XAxis and in the Trend line as well;

Basically have changed the trend measure to use WeekofYearKey instead of the Year - Week

and used these in the axis like so:

It turned out terribly wrong, the X axis assumes there must be a period between 202152 and 202201 that should be accounted for in the visualization AND in top of that, the trend line also has 3 different slopes:

1. one before the period without data entries 
2. one in the imaginary "weeks" between 202152 and 202201
3. another from 202201 on... 

Any other ideas?

Thank you

[johnt75]

You could create a week commencing column in the date table, like

[Date] - WEEKDAY([Date],2) + 1,

You can use that in the measure, not sure if you would have to use it on the axis as well or whether you could use the year week column.

[Anonymous]

That didn't work either.

What I am currently trying is:

Have created and index for all the Year-week's, in my time interval.

This index behaves as in the following example:

Year Week	Sequencial index
2021 01	1
2021 02	2
...	...
2021 52	52
2022 01	53
2022 02	54
2022 03	55
...	...
2022 51	103
2022 52	104

Now, I'm using this sequential index in the X axis, and as it is continuous, it allows me to use the default trendline available in the 'Analytics' panel.

However, now the challenge is, how do I make it show the corresponding Year Week, instead of the index?

How my DateTable looks now:

and how using the sequential index reflects on the Production output table and trendline:

Now I would just need to be able to show Year week in the X axis, in a purely cosmetic way, instead of this ugly index. Any ideas?

Thanks!

[johnt75]

It might be possible to keep the sequential week number in the calculation and use the year week column on the chart axis

[Anonymous]

Wait, I have messed up in keeping X-Axis as type continuum. But changing it to categorical still didn't solve the trend slope part unfortunately.