The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
@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
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)
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
@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
)
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
55 | |
50 | |
36 | |
33 | |
28 |