Forum Discussion
Trendline needed
Thanks, i was.
Still not working though:
VAR Known =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(Date2[Date]),
"Known[X]", Date2[Date],
"Known[Y]", 'Cases'[DISTINCT COUNT & SUM]
),
AND(
NOT(ISBLANK(Known[X])),
NOT(ISBLANK(Known[Y]))
)
)
VAR SlopeIntercept =
LINESTX(Known, Known[Y], Known[X])
VAR Slope =
SELECTCOLUMNS(SlopeIntercept, "Slope", [Slope1])
VAR Intercept =
SELECTCOLUMNS(SlopeIntercept, "Intercept", [Intercept])
RETURN
AVERAGEX(
DISTINCT('Date2[Date]),
Intercept + Slope * Table[Date]
))Anonymous
It looks like you're using a measure at the Intercept + Slope * measure in the RETURN section. You want to use your column [Progress_date] not a measure.
Anonymous
When you use LINEST or LINESTX it creates a single row with ten columns. In the measure that I provided we are creating a variable using LINESTX. The red lines under [Slope1] and [Intercept] is becasue it doesn't recognize those as columns until you've finished creating the measure. Those variables get calculated in order from top to bottom.
I see it now. VAR Known is the name of your measure. Name your measure something else and move the VAR Known in front of the FILTER.
Almost there, thanks for persevering with me on this!
Line 22 = DISTINCT('Date2[Date]),
Trendline =VAR Known =FILTER(SELECTCOLUMNS(ALLSELECTED(Date2[Date]),"Known[X]", Date2[Date],"Known[Y]", 'Cases'[DISTINCT COUNT & SUM]),AND(NOT(ISBLANK(Known[X])),NOT(ISBLANK(Known[Y]))))VAR SlopeIntercept =LINESTX(Known, Known[Y], Known[X])VAR Slope =SELECTCOLUMNS(SlopeIntercept, "Slope", [Slope1])VAR Intercept =SELECTCOLUMNS(SlopeIntercept, "Intercept", [Intercept])RETURNAVERAGEX(DISTINCT('Date2[Date]),Intercept + Slope * Date2[Date]))))))Slope1 and Intercept are underlined in Red (Cannot find name messages)
No need to worry about this.
You can use LINESTX or LINEST by themselves and create new tables. The table will create a single row with 10 columns. Two of those columns are Slope1 and Intercept.Yes that fixed it, I should have spotted that myself.
However, I know get this, there is something up with Slop1 & Intercept as they are marked RED
Thanks, I've finally got it to work 😊
I've notice the trendline is far steeper with your Variable (see solid line in top line graph) compared to when I use the inbuilt Trendline using the Continuous x Axis date range. This inbuilt range has every other month missing which is why I'm desperate for an alternative option.
I guess I need to decide which one to use. My only other option is to Snip an image of the the x axis with complete months and paste them over in Powerpoint when this get presented, incredible we still have to do this in 2023!
Thanks for all of your help!
with same Y Axis set at Zero:
Thanks for helping me with this, much appreciated!
Hi may I ask how did you fix that? cause i get the same error..
Anonymous
Are you talking about the red lines under [Slope1] and [Intercept]? If so, there is nothing to fix. You measure should still work without any issues. If you're getting an error when you try to create the measure then that is potentially a different issue.
yes i have the red line under Slope1 and Intercept,
but If i dont fix it its showing this error visual..
Anonymous
What do the details say?
It's saying ...🤔
Anonymous
Can you screenshot your measure?
Yes,
ok , but may I ask why there is the red line under my [Slope1] and [intercept]? thanks..
as per screenshot, one is far steeper than the other. Its fine though, I can use either