Search code examples
powerbidaxpowerquery

Forecasting Multiple Linear Regression Trends on Same Time Series Data Visualization

I am trying to establish two forecasting lines on the same data visualization in a PowerBI dashboard. I will provide two images in this post. Image A shows the data table that I imported into PowerBI from Excel. This data table holds the fake data that I would like to use in my forecasts. Image B shows the time scaled data visualization that I would like to overlay with linear regressions. I have tried to solve this problem in both DAX and m code but have found no success. I can post my DAX attempts, if requested. I just wanted to get a fresh perspective on the problem/solution space. Can someone help develop a DAX solution to overlay multiple linear regression models over the sums of spent and remaining for forecasting purposes?

Project WBS Job Order Report Date Planned Spent Remaining
A 1.2.3 12345 1/1/2023 $ 50,000.00 $ 6,000.00 $ 44,000.00
B 1.2.4 12346 1/1/2023 $ 45,000.00 $ 4,000.00 $ 41,000.00
C 1.2.5 12347 1/1/2023 $ 100,000.00 $ 13,000.00 $ 87,000.00
A 1.2.3 12345 2/1/2023 $ 50,000.00 $ 9,000.00 $ 41,000.00
B 1.2.4 12346 2/1/2023 $ 45,000.00 $ 6,000.00 $ 39,000.00
C 1.2.5 12347 2/1/2023 $ 100,000.00 $ 19,500.00 $ 80,500.00
A 1.2.3 12345 3/4/2023 $ 50,000.00 $ 13,500.00 $ 36,500.00
B 1.2.4 12346 3/4/2023 $ 45,000.00 $ 9,000.00 $ 36,000.00
C 1.2.5 12347 3/4/2023 $ 100,000.00 $ 29,250.00 $ 70,750.00
A 1.2.3 12345 4/4/2023 $ 50,000.00 $ 20,250.00 $ 29,750.00
B 1.2.4 12346 4/4/2023 $ 45,000.00 $ 13,500.00 $ 31,500.00
C 1.2.5 12347 4/4/2023 $ 100,000.00 $ 43,875.00 $ 56,125.00
A 1.2.3 12345 5/5/2023 $ 50,000.00 $ 30,375.00 $ 19,625.00
B 1.2.4 12346 5/5/2023 $ 45,000.00 $ 20,250.00 $ 24,750.00
C 1.2.5 12347 5/5/2023 $ 100,000.00 $ 65,812.50 $ 34,187.50

Image B

Image Ar.com/GOSl9.png

Solution

  • Perhaps I misunderstood. Is this what you expect, with separate linear trend lines for each Spent and Remaining columns?

    enter image description here

    If so, after creating the line chart:

    • Select the analytics pane
    • Enable the trendline option
    • turn the combine series option to Off

    enter image description here

    I don't think you can obtain the values of m and b from the visualization directly. But you can use the DAX LINESTX function to extract them from the data, or you can use a custome Power Query function.

    For DAX, select New Table from the Calculations tab.

    Enter this formula for the table, (where Table refers to your original data table above)

    Stats = LINEST('Table'[Spent], 'Table'[Report Date])

    This will generate a new table with a number of statistics for Spent. m = Slope1 and b = Intercept

    For Remaining, make the obvious substitution.

    So for m and b we show this:

    enter image description here

    Edit: Custom Power Query Function added
    Note this is only for a simple linear regression
    For a solution using M try this custom function: The code returns a two element list with the first element being the slope m, and the second the intercept b

    (x as list, y as list)=>

let
    tbl = Table.FromColumns({x,y},{"x","y"}),

//If you pass both lists as numbers, this next line is not necessary
    #"Date to Number" = Table.TransformColumns(tbl,{"x", each Number.From(_), type number}),


    #"X*Y" = Table.AddColumn(#"Date to Number","x*y", each[x] * [y], type number),
    #"X²" = Table.AddColumn(#"X*Y", "x²", each Number.Power([x],2), type number),
    #"Y²" = Table.AddColumn(#"X²","y²", each Number.Power([y],2),type number),
    N = Table.RowCount(#"Y²"),
    #"ΣX" = List.Sum(#"Y²"[x]),
    #"ΣY" = List.Sum(#"Y²"[y]),
    #"ΣX*Y" = List.Sum(#"Y²"[#"x*y"]),
    #"ΣX²" =List.Sum(#"Y²"[#"x²"]),
    #"ΣY²" = List.Sum(#"Y²"[#"y²"]),
    b = ((ΣY * #"ΣX²") - (ΣX * #"ΣX*Y"))  /  ((N * #"ΣX²") - (ΣX * ΣX)),
    m =  (N * #"ΣX*Y" - ΣX * ΣY) / (N * #"ΣX²" - (ΣX * ΣX))
in
    {m,b}

    Given your original data, the above returns:

    enter image description here