Forum Discussion
Help with Linear Trend line ?
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 Ritaf1983
I have used your Trend for Goals Scored, but still getting same result.
Will you have a look on the sample file.
https://github.com/suvechha/samplepbi/blob/main/sampleTrend.pbix
The Excel provide Linear Trendline like
v-tejrama still needs help as not getting the correct result.
Hi ashmitp869 ,
We kindly suggest raising a support ticket with the partners group: Power BI Partners | Microsoft Power Platform as they will be able to investigate the matter in detail and provide deeper assistance. I also tried several workarounds on my side, but I was unable to change the measure in the PBIX file you provided, so involving the supporting partners group would be the best next step.
Thank you.Hi ashmitp869 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.I have used your Trend for Goals Scored, but still getting same result.
Will you have a look on the sample file.
https://github.com/suvechha/samplepbi/blob/main/sampleTrend.pbix
The Excel provide Linear Trendline like@v-tejrama still needs help as not getting the correct result.
Hi ashmitp869 ,
I am following up to check if you have been able to contact Partners Group. Please let me know if you require any support from me to proceed.
Thank you.
