Forecast Line Using Custom Measure

[Luke-Howells]

Hi Experts,

So I have a measure which will work out a Loss Ratio based off claims and income. What I want to do is create a forecast line for the months that have not yet happened in the year. The forecast is based off the monthly movements from the years prior and then take an average of the movements for the months. I have already worked this part out with the below measure:

Measure to work out the monthly movement. This is the current month - month before and then averaged out over 4 years. 

AAA AVG LR Monthly Movement = 
(
--2021
CALCULATE(([AAA Net Incurred Loss Ratio] - [AAA Net Incurred Loss Ratio +1]),'02. UWY Year'[Underwriting Year] = (YEAR(TODAY())-1))

+

--2020
CALCULATE(([AAA Net Incurred Loss Ratio] - [AAA Net Incurred Loss Ratio +1]),'02. UWY Year'[Underwriting Year] = (YEAR(TODAY())-2))

+

--2019
CALCULATE(([AAA Net Incurred Loss Ratio] - [AAA Net Incurred Loss Ratio +1]),'02. UWY Year'[Underwriting Year] = (YEAR(TODAY())-3))

+

--2018
CALCULATE(([AAA Net Incurred Loss Ratio] - [AAA Net Incurred Loss Ratio +1]),'02. UWY Year'[Underwriting Year] = (YEAR(TODAY())-4))
)

/ 4

Using this measure and the Loss Ratio I already have, I want to be able to create a measure which will forecast the rest of the year. 

The forecast would be, for the first month, the loss ratio for the month prior plus the AVG Movement. The following months would be the last available loss ratio plus the AVG Movement for that month and the months prior where there is no Loss Ratio. (Hope that makes sense). In the table below are the results the measure should show. The ones in bold are for the months I currently don't have.

Month	Result
1	2.7%
2	7.3%
3	12.3%
4	16.0%
5	20.2%
6	25.9%
7	29.3%
8	29.8%
9	33.3%
10	36.3%
11	38.9%
12	42.6%
13	47.6%
14	51.8%
15	55.7%
16	58.9%
17	62.2%
18	65.2%
19	67.8%
20	69.8%
21	70.7%
22	71.2%
23	71.3%
24	71.4%

Any help on this would be appreciated. I have a feeling there is a way to do this, but just can't figure it out 🤦‍♂️

TIA

[v-henryk-mstf]

Hi @Luke-Howells ,

I can't reproduce this question for now, can you provide some test data and describe it, so that I can answer it for you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry