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....
  • Juan-Power-bi's avatar
    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.