Forum Discussion

nleuck_101's avatar
nleuck_101
Continued Contributor
3 months ago
Solved

Forecast total display

Hello All,

 

I have a forecast model on an area chart. I would like to display the final number for the end of the year but whenever I do I get a number that doesn't match my end of the year total. I want to display the 632 in a card but when I try to display my measure in the card it displays 115K, which doesn't even make sense. Below is a screenshot and my DAX formula. I appreciate any help!

 

DAX formula for forecast
VAR _data =
FILTER(SELECTEDCOLUMNS(ALLSELECTED(Table),"x_values", Table[date], "y_values", [Running Total measure]), AND(NOT(ISBLANK([x_values])),NOT(ISBLANK([y_values]))))
VAR y_sum = SUMX(_data, [y_values]
VAR x_sum = SUMX(_data, [x_values]
VAR x_sqr = SUMX(_data, [x_values]^2)
VAR xy = SUMX(_data, [x_values] * [y_values])
VAR n = COUNTROWS(_data)

VAR Intercept = (y_sum * x_sqr - x_sum * xy) / (n * x_sqr - (x_sum)^2)
VAR Slope = (n * xy - x_sum * y_sum) / (n * x_sqr - (x_sum)^2)

VAR Regression = SUMX(TableForecast, Slope * TableForecast[ForecastDate] + Intercept)

RETURN
Regression

Need Help
  • Juan-Power-bi's avatar
    Juan-Power-bi
    3 months ago

    i see something wrong in the  last line of your measure. You're using SUMX(TableForecast, Slope * TableForecast[ForecastDate] + Intercept) — this iterates over every row in TableForecast and sums all the predicted values, which is why you're getting 115K instead of the end-of-year number. You're summing the entire forecast line, not just the last point.

     

    To get just the year-end value, you need to calculate the regression for a single specific date, the last date in your forecast table instead of summing across all of them:
    daxVAR LastForecastDate = MAX(TableForecast[ForecastDate])
    RETURN
    Slope * LastForecastDate + Intercept
    Replace the SUMX line and the RETURN Regression with that. The card will then show the predicted value at the end of the forecast period rather than the sum of all predicted values across the year.

3 Replies

  • Juan-Power-bi's avatar
    Juan-Power-bi
    Super User
    3 months ago

    i see something wrong in the  last line of your measure. You're using SUMX(TableForecast, Slope * TableForecast[ForecastDate] + Intercept) — this iterates over every row in TableForecast and sums all the predicted values, which is why you're getting 115K instead of the end-of-year number. You're summing the entire forecast line, not just the last point.

     

    To get just the year-end value, you need to calculate the regression for a single specific date, the last date in your forecast table instead of summing across all of them:
    daxVAR LastForecastDate = MAX(TableForecast[ForecastDate])
    RETURN
    Slope * LastForecastDate + Intercept
    Replace the SUMX line and the RETURN Regression with that. The card will then show the predicted value at the end of the forecast period rather than the sum of all predicted values across the year.

  • Natarajan_M's avatar
    Natarajan_M
    Super User
    3 months ago

    Hi nleuck_101 , Can you share the sample data or the pbix file with the sample data so we can explore this in detail? 

    Thanks!

    Natarajan Manivasagan

    If you found this helpful, please consider giving it a Kudos and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

     

    🏆 Best Solution for Enterprise BI — 2026 Microsoft Fabric Semantic Link Developer Experience Challenge
    👉 Microsoft announcement · View the winning notebook

     

    For more Power BI tips and discussions, let's connect on LinkedIn.

     

    Cheers!