Forum Discussion

SHS's avatar
SHS
Icon for Resolver I rankResolver I
5 years ago

Forecasting on Cumulative Total not Working

Hello,

 

I'm doing a forecast on vessel population development in which I have some issues in one of my measures (cumulative or forecasting measure). 

 

I have the vessel population data with the population build year ranging from 1868 to 2021 (with some years, no new vessels built). Additionally, I have built a Calendar Table that should include the date range + 5 additional years (5 year forecasting), which also seems to work fine (One-way Relationship -> DateTable to Population Data).

 

The Cumulative Sum:

Cumulative Total =
VAR Max_Year = CALCULATE(MAX(DimCalendar[Date]))
VAR Result = CALCULATE(SUM('Forecast_MASTER (View033)'[Vessel Count]),DimCalendar[Date] <= Max_Year)
RETURN
Result
 
The Forecast Measure (Based on a 5-year Average)
Forecast v2 =
VAR Population4YearsAgo = CALCULATE([Cumulative v3],DATEADD(DimCalendar[Date],-4,YEAR))
VAR Population3YearsAgo = CALCULATE([Cumulative v3],DATEADD(DimCalendar[Date],-3,YEAR))
VAR Population2YearsAgo = CALCULATE([Cumulative v3],DATEADD(DimCalendar[Date],-2,YEAR))
VAR PopulationLastYear = CALCULATE([Cumulative v3],DATEADD(DimCalendar[Date],-1,YEAR))
VAR PopulationThisYear = CALCULATE([Cumulative v3],YEAR(DimCalendar[Date])=YEAR(TODAY()))
VAR FactorLoading = SELECTEDVALUE(Factor[Factor])

RETURN
DIVIDE(Population4YearsAgo + Population3YearsAgo + Population2YearsAgo,3,0)*(1+FactorLoading)
 
Remaining Forecast (Should only show the forecast figures after year 2021 (2022-2026):
Remaining Forecast =
VAR CurrentNBYear =
CALCULATE(
MAX('Forecast_MASTER (View033)'[YearOfBuild]),
ALLEXCEPT('Forecast_MASTER (View033)','Forecast_MASTER (View033)'[Source])
)
VAR Result =
CALCULATE(
[Forecast v2],
KEEPFILTERS(DimCalendar[Date] > CurrentNBYear)
)

RETURN
Result
 
However, the remaining forecast still shows figures prior to 2022 - from 1868 to 2021, remaining forecast is constant showing "29".
Looking at my forecast measure, I can see that the forecast measure seems to behave incorrectly in the first 4 years.
 
Additionally, due to this error, when I want to combine actual development with future forecast I simply add a SUM of these (as development should include all up to 2021, and forecast after 2021), but this is summing across all years due to results found in both measures.
 
Hope that someone can help me forward so I can go on vacation with a clear mind. 
 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    you calculate variables for last year and this year but then don't use them in the result. Please explain.

    If you do a five year average then you can use the x-4 and x numbers and divide the difference by 5 to get the average yearly trend. No need for all the intermediate years.