Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Trendline Calculation help

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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