Trendline Calculation help

[michellen]

Hi all

I have a data for July-Sept and have used a trendline calculation but the trendline is calculating values in months that don't have any data. We are using our own calculation to align to a Trend % change calculation on the page so haven't used the inbuilt one in Analysis. Can anyone advise what I've done wrong in the calculation below? 

Trendline_Measure =

VAR Known =

FILTER (SELECTCOLUMNS (

CALCULATETABLE (VALUES ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar') ),

"Known[X]", 'Calendar'[Date],

"Known[Y]", [Event] ),

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 ( 'Calendar'[Date] ), Intercept + Slope * 'Calendar'[Date] )

[amitchandak]

@michellen , You are doing a product with * 'Calendar'[Date]?

is the last return correct ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[michellen]

Thanks @amitchandak
Link to file with basic data. My formula is extending the trendline beyond the dates of the data. Want to only show a trend between the data points. I'm no guru on trend formulas and copied it from PBI Community. Any help would be appreciated!
Trendline_DAX.pbix