Formula for trendline

Someone lese asked for it in May - see the comments on https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6998768-ability-to-add-trend-line-to-charts

You can likely do it in R until/if that feature is implemented in native Power BI visuals - e.g. http://stackoverflow.com/questions/24882209/how-to-get-trendline-equations-in-r

Hi there,

this function replicates the Excel-Trend-function, just without the possiblity to define your own slope and intercept:

 (YList as list, NoOfIntervalls as number) =>
let
Source = Table.FromColumns({YList}),
xAxis = Table.AddIndexColumn(Source, "Index", 1, 1),
Rename1 = Table.RenameColumns(xAxis,{{"Column1", "y"}, {"Index", "x"}}),
AvgX = List.Average(Rename1[x]),
AvgY = List.Average(Rename1[y]),
x = Table.AddColumn(Rename1, "xX", each [x]-List.Average(Rename1[x])),
y = Table.AddColumn(x, "yY", each [y]-List.Average(x[y])),
xy = Table.AddColumn(y, "xy", each [xX]*[yY]),
xXx = Table.AddColumn(xy, "xXx", each [xX]*[xX]),
a = List.Sum(xXx[xy])/List.Sum(xXx[xXx]),
b = AvgY-(a*AvgX),
ListIntervalls = {List.Max(Rename1[x])+1..List.Max(Rename1[x])+NoOfIntervalls},
TableIntervalls = Table.FromList(ListIntervalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Rename = Table.RenameColumns(TableIntervalls,{{"Column1", "x"}}),
Values = Table.AddColumn(Rename, "y", each [x]*a+b),
TREND = Table.Combine({Rename1,Values})
in
TREND