Daily DAX : Day 372 LINEST

🔢 Power BI DAX: LINEST Function

📝 What is LINEST?

LINEST calculates the linear regression statistics for a set of X and Y values. It returns an array of values including slope, intercept, R-squared, and more!

💻 Syntax

LINEST(known_y's, [known_x's], [const], [stats])

Parameter	Description	Required?
known_y's	Array of Y values (dependent variable)	✅ Yes
known_x's	Array of X values (independent variable)	❌ Optional
const	TRUE = force intercept=0, FALSE = calculate intercept	❌ Optional
stats	TRUE = full statistics, FALSE = just slope/intercept	❌ Optional

📊 What It Returns

When stats = TRUE, LINEST returns a 5x2 array:

	Column 1	Column 2
Row 1	Slope	Standard Error (Slope)
Row 2	Y-Intercept	Standard Error (Intercept)
Row 3	R²	Standard Error (Y)
Row 4	F-Statistic	Degrees of Freedom
Row 5	SS Regression	SS Residual

💡 Simple Example

Scenario: Sales vs. Advertising Spend

Sales = {100, 150, 200, 250, 300}
AdSpend = {10, 15, 20, 25, 30}

DAX Formula:

RegressionStats = LINEST(Sales, AdSpend, TRUE, TRUE)

Result: Slope = 10 (each $1 ad spend = $10 sales increase)

🎯 Real Use Cases

1. 📈 Sales Forecasting: Predict future sales based on past ad spend
2. 🏠 Price Prediction: House price vs. square footage
3. 📱 App Growth: Downloads vs. marketing budget
4. ⚡ Energy Analysis: Electricity usage vs. temperature
5. 🏦 ROI Analysis: Investment return vs. initial capital

🚀 Quick Implementation Steps

1. Create Measures for X and Y values
2. Write: TrendLine = LINEST([Y_Measure], [X_Measure], TRUE, FALSE)
3. Use INDEX to extract: Slope = INDEX([TrendLine], 1, 1)
4. Add to visuals for predictions!

💡 Pro Tip: Combine with TREND() function for actual predictions!