Forum Discussion
Helper IILinear regression issue - straight line on the graph
Hi All,
I have an issue with linear regression line. I have changed the source of my data from manualy populated excel spreadsheets into data from SQL server. Due to this I have changed relation a bit. Previously data were connected through sub table with calendar by WC_Date column which show year, month and week (ex. 2025P10WK41). At this moment, data are in relation with calendar by "Date Column". I'm still using WC_Month column from calendar table (ex. Oct 2025) on X axis in my visualisation.
Due to this change trend line is always straight. Previously everything worked fine. Trend line is dot black line on below picture.
I have tried to modify DAX to replace WC_Month column to Date column but then trend line is not a line. It's curve which totaly overlaping blue line.
Could you please guide me how to modify below DAX to get proper line which shows the trend ?
COQ12M_TREND =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'COQ Calendar'[WC_Month] ),
"Known[X]", 'COQ Calendar'[WC_Month],
"Known[Y]", [COQ12M_v2]
),
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] ^ 2 )
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 ^ 2
)
VAR Intercept =
Average_Y - Slope * Average_X
VAR MaxDate =
MAX ( 'DATE_DUP'[WC_Month] )
VAR MinDate =
DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) )
RETURN
CALCULATE (
SUMX (
DISTINCT ( 'COQ Calendar'[WC_Month] ),
Intercept + Slope * 'COQ Calendar'[WC_Month]
),
FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate )
)6 Replies
- JamieHolding
Resolver I
From a first look, I think it may be because of treating WC_Month as a value (would be a very high value around 40,000) which means it doesn't vary much across the x axis.
Proposed this to AI and it suggested this as an alternativeCOQ12M_TREND = VAR Known = FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'COQ Calendar', 'COQ Calendar'[WC_Month] ), "Index", RANKX ( ALL ( 'COQ Calendar'[WC_Month] ), 'COQ Calendar'[WC_Month], , ASC ), "Y", [COQ12M_v2] ), NOT ( ISBLANK ( [Y] ) ) ) VAR Count_Items = COUNTROWS ( Known ) VAR Sum_X = SUMX ( Known, [Index] ) VAR Sum_X2 = SUMX ( Known, [Index] ^ 2 ) VAR Sum_Y = SUMX ( Known, [Y] ) VAR Sum_XY = SUMX ( Known, [Index] * [Y] ) VAR Slope = DIVIDE ( Count_Items * Sum_XY - Sum_X * Sum_Y, Count_Items * Sum_X2 - Sum_X ^ 2, 0 ) VAR Intercept = AVERAGEX ( Known, [Y] ) - Slope * AVERAGEX ( Known, [Index] ) VAR MaxDate = MAX ( 'COQ Calendar'[WC_Month] ) VAR MinDate = DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) ) VAR SelectedMonths = FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate ) VAR MaxIndex = COUNTROWS ( SelectedMonths ) RETURN Intercept + Slope * MaxIndex Hi MKPartner ,
Thank you for posting the question in Microsoft Fabric Community and thanks to JamieHolding for the response and alternative shared.
As noted, regression calculations require a numeric and continuous X axis. When month or date fields are used directly at a grouped level, the resulting trend can appear flat. Using a sequential numeric index for the calculation while keeping the month field only for display addresses this behavior.
Related discussion:
Linear regression - Challenges with trend line at ... - Microsoft Fabric Community
If the issue continues after applying this approach, please share a small sample of the data or a PBIX file (with sensitive information removed). You can upload the file to a cloud storage location such as OneDrive, Dropbox, Google Drive, or WeTransfer and paste the download link in the thread for review.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Hope this helps. Please reach out for further assistance.
Thank you.
MKPartnerThank you for guidance. I'm trying to understand this and I have tweaked my DAX code based on [Month Index Number] column and always got the graph. The trend line is overlaping values.
NEW_COQ12M_TREND = VAR Known = FILTER ( SELECTCOLUMNS ( ALLSELECTED ( 'COQ Calendar'[Month Index (related)] ), "Known[X]", 'COQ Calendar'[Month Index (related)], "Known[Y]", [COQ12M_v2] ), 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] ^ 2 ) 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 ^ 2 ) VAR Intercept = Average_Y - Slope * Average_X VAR MaxDate = MAX ( 'DATE_DUP'[WC_Month] ) VAR MinDate = DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) ) RETURN CALCULATE ( SUMX ( DISTINCT ( 'COQ Calendar'[Month Index (related)] ), Intercept + Slope * 'COQ Calendar'[Month Index (related)] ), FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate ) )Not sure why it worked properly before data source change.
Here is my calendar table with visible WC_Month dax code:
Perhaps I should create WC_Date column like 2025P10WK41 like in previous data source and make relation through this ?
Hi MKPartner ,
Thanks for sharing the updated DAX and the calendar table.
The regression calculation is working, but the trend line overlaps the base values because the measure is evaluated at the same month level as the original metric. When the regression result is returned once per month and plotted on a month based axis, this overlap is expected.
This also explains why it appeared to work differently before the data source change. The earlier WC based key behaved like a sequential index, so the regression and visual context aligned implicitly. With a date based calendar, that implicit behavior no longer applies.
Recreating a WC_Date column in a format such as 2025P10WK41 may make the trend appear correct again because it behaves like a sequential index, but this is incidental. A date based calendar relationship remains the recommended design.
As a quick check, please confirm that the Month Index is used only in the calculation, WC_Month is sorted by the index, the X axis is set to categorical, and the calendar context evaluates to one row per month.
If the issue persists, please share a small sample dataset or a PBIX file with sensitive information removed, uploaded to a cloud storage location, and include the link in the thread so it can be reviewed further.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Thank you.
Hi MKPartner ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.Additionally, could you please share sample dataset or a PBIX file with sensitive information removed, uploaded to a cloud storage location, and include the link in the thread so it can be reviewed further.
Thank you.
