Calculation for the Trend Line Measure is below. The aim is to go from the last closed period back and calculate a linear trend line. This will show what hapopens in the futuire as well.

In addition I need to be able to provide a measure that calculates the trend from the current period to the end of the year.

The tremd line works in Matrix and chart but getting the sum of the values just does not work Placed the measure for the totals at the bottom.

Thanks

E

**TrendLine =**

//Simple linear regression =

// y = slope +b(intercept)

//Simple linear regression =

// y = slope +b(intercept)

VAR MonthsForTrend = 12

Var StartPeriod = -1 * (MonthsForTrend)

VAR Known =

FILTER (

SELECTCOLUMNS (

FILTER ( ALL(‘FYear’),‘FYear’[Fiscal Month offset] >= StartPeriod && ‘FYear’[Fiscal Month offset] <= -1

)

, “Known[X]”, ‘FYear’[Fiscal month offset]

, “Known[Y]”

, [Sales]

),

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] ^ 2 )

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 ^ 2 )

VAR Intercept = Average_Y - Slope * Average_X

RETURN

```
SUMX (
DISTINCT ( 'FYear'[Fiscal Month offset] ),
Intercept + (Slope * 'FYear'[Fiscal Month offset])
)
```

**TrendLine YTG =**

VAR ReturnValue = CALCULATE(

[TrendLine]

, REMOVEFILTERS(‘FYEAR’)

,‘FYEAR’[Month Offset >=0 && 'FiYEAR[Month Offset] <=6

)