Extract End of Year Values of a linear regression.

[LoicIDCC]

Dear community,

I calculate a linear regression of a measure ( CumulativePATT_ID ) using the values of a certain number of previous months from a calendar table 'CALENDAR'[Year_Month] (Year_Month = date(year('CALENDAR'[Date]),MONTH('CALENDAR'[Date]),1)))

I want to create a new table by year, that extracts the end-of-year value of the regression (with the same slope and intercept).

How do I do that?

Thanks!

Regression by Year and Month = VAR Known = FILTER ( SELECTCOLUMNS ( FILTER(ALL('CALENDAR'[Year_Month]),'CALENDAR'[Year_Month]<=TODAY()), "Known[X]", 'CALENDAR'[Year_Month], "Known[Y]", [CumulativePATT_ID] ), 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 ( 'CALENDAR'[Year_Month]), Intercept + Slope * 'CALENDAR'[Year_Month] )

[Greg_Deckler]

@LoicIDCC Right, so as part of your linear regression you know the slope (m) and y intercept, correct? So just plug that back into the formula for slope. x2 is the number of months after your y intercept or whatever y value you want to start with. y2 is your unknown but everything else should be known in that formula, correct?

[Greg_Deckler]

@LoicIDCC I have an example of this in my book, DAX Cookbook. It is recipe 8 in Chapter 12. You can get the PBIX here: gdeckler/DAXCookbook (github.com)

But, if you know the slope and y-intercept, then the formula is m = (y2 - y1) / (x2 - x1) so you would want to solve that equation for y2, or y2 = m(x2 - x1) + y1. If you use y1 as your y intercept, x1 would be 0 so y2 = m * x2 + y1.

[LoicIDCC]

Sorry but I still don't understand.

Here is my file with some dummy data.: Report link 

I need the second chart by year, with the EOY value for each year.

[v-kkf-msft · Accepted solution]

Hi @LoicIDCC ,

Try the following formula:

Table = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT('CALENDAR'[Date].[Year]),
        DISTINCT(Data[Category])
    ),
    "Running Total", CALCULATE(
            DISTINCTCOUNT(Data[Unique_ID]),
            FILTER(
                ALL('Data'),
                Data[Category] = EARLIER([Category])
                && 'Data'[Date] <= DATE( EARLIER([Year]), 12, 31 )
            )
        )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[LoicIDCC]

Thanks for your example but I don't have a problem with the linear regression itself.

My linear regression is calculated by month. 

I want to create a measure by year, that is a filtered table of the linear regression by month, with the value at the end of each year.