Help with Linear Trend line ?

ashmitp869 maybe try to change currentx value to this:

 

VAR CurrentX = RANKX(ALL('Date'[Year-Month Code], 'Date'[MonthYear]), CALCULATE( MAX('Date'[Year-Month Code])), , DESC) 

Hi ashmitp869 

From what I see, the trend lines are much lower than the actual values, which indicates a calculation issue.
This usually happens because the formula removes filters from Month-Year, but columns that are indirectly related to that field (like a column used for sorting) still participate in the query behind the scenes.
So even if the visual doesn’t directly use the month column, it still filters the calculation context.
That’s why when you want to manipulate context involving Month-Year, it’s better either to include the related columns as well, or to operate at the Calendar table level — for example, like in my formula below:

 

Trend for Goals Scored =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Calendar' ),
"KnownX", 'Calendar'[yy-mm],
"KnownY", [Sales]
),
NOT ( ISBLANK ( [KnownX] ) )
&& NOT ( ISBLANK ( [KnownY] ) )
)
VAR SlopeIntercept =
LINESTX ( Known, [KnownY], [KnownX] )
VAR Slope =
SELECTCOLUMNS ( SlopeIntercept, "Slope", [Slope1] )
VAR Intercept =
SELECTCOLUMNS ( SlopeIntercept, "Intercept", [Intercept] )
RETURN
SUMX (
DISTINCT ( 'Calendar'[yy-mm] ),
Intercept + Slope * 'Calendar'[yy-mm]
)

The pbix with the example is attachd

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

Hi ashmitp869 ,

Thank you Ritaf1983  and parry2k for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

 

Thank you.