topic Re: Trendline Calculation help in DAX Commands and Tips

Trendline Calculation help

Anonymous — Wed, 06 Oct 2021 06:57:05 GMT

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

Re: Trendline Calculation help

amitchandak — Wed, 06 Oct 2021 08:25:29 GMT

Anonymous , 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.

Re: Trendline Calculation help

Anonymous — Thu, 07 Oct 2021 06:04:11 GMT

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