"Rolling" Forecast for warranty claims depending on our manufacturing volume

[Fraistan]

Hi, 

I work with warranty claims and I would like to be able to forecast the future costs. 

This is the link to the demo-data. 

I have 4 sheets: 

Volumes: Consists of date when it's manufactured, volume, which plant and what product. 

Static Value: is a value that changes every year, it's a value that we extract for every product we make to be able to compensate for future warranty claims.
[Volume * Static Value = Money to compensate warranty claims]

Forecast DimDate: DimDate table made for the future. 

Distribution Curve: Curve that "assumes" that for every month further in, the claims/cost will increase. By the end of warranty period, we assume that 100% of the [Money to compensate warranty claims] is used. 

Today we have 5 years warranty (60 months) on our products. That's why it needs to be "rolling", not sure if that is correct term. But see below example in Matrix.

I would like to visualise something like this in a Matrix, 5 years ahead, but the "next" year is the most important from a budget perspective.
The "Forecast Calc." is not necessary, just need it for this example to be pedagogical. 

Year	Month	Manufacturing Volume	Static value	Compensate money (lifetime)	DistributionC.	Forecast Calc.	Forecast
2024	Jan	1000	130	130000	1%	1300	1300
	Feb	1100	130	143000	2%	1430+1300	1730
	Mar	1200	130	156000	4%	1730+1430+2600	5760
	Apr	1300	130	169000	5%
	Jun	1400	130	182000	6%	....
	Jul	1500	130	195000	8%	....
	Aug	1600	130	208000	10%	....
	Sep	1700	130	221000	12%	....
	Oct	1800	130	234000	15%	....
	Nov	1900	130	247000	17%	....
	Dec	2000	130	260000	20%	....
2025	Jan	2100	140	294000	25%

I hope this make sense. I've been able to do this based on historical data (Expected cost vs actual cost) every month. But when I tried to reverse it, It didn't work. I guess I need a new set of eyes on this problem. 

[Anonymous]

@johnbasha33  Thanks for your contribution on this thread.

Hi @Fraistan ,

It seems like I have no sufficient permission to access to your shared file. Could you please grant me the proper permission to access it? Thank you.

In addition, do you want to get the field [Forecast]? What's the calculation logic for this field? Could you please explain it base on the provided sample data? It would be helpful to get the solution. Thank you.

Best Regards

[Fraistan]

Thanks V-Yiruan-msft to take on this challange! 

I've given you access now 🙂 

The last page "What to get" shows the desired result. 
But it should be:
Volume * Static Value * Distribution Curve

Only been visualised on 1 year, but keep in mind it should be up to 5 years (60 months/periods)

[Anonymous]

Hi @Fraistan ,

I still can't access your shared file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[johnbasha33]

@Fraistan 

Static Value per Month:
StaticValuePerMonth = 'Static Value'[Value] / 12

Forecast Calculation (Cumulative):
ForecastedCost =
VAR SelectedDate = MAX('Forecast DimDate'[Date])
RETURN
CALCULATE(
SUM('Distribution Curve'[Percentage] * 'Static Value'[Value]),
FILTER(
ALL('Forecast DimDate'),
'Forecast DimDate'[Date] <= SelectedDate
)
)

Vsualizing in Power BI

• Drag the Year and Month from your Forecast DimDate table to the Rows of the Matrix visualization.
• Add the ForecastedCost measure to the Values section of the Matrix.
• You can also add other dimensions such as Manufacturing Volume if needed.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Fraistan]

Hi Johnbasha33, 

Thanks for answer, I have tried your solution but I don't think it was the correct one 😞 

I've made a new page in my excel that is called "what to get". That's basically what I want. 
But we need to keep in mind that period 60 is the last month to calculate it.

I have only done it for 1 year, but it should be up to 5 years.