All-time % change where the beginning year changes based on state

[LukeBrowneUK]

Hello,

I am currently working on a project regarding premiums in the US.

I need to show an all time change (%) on the average limit any one unit for each state from when we starting taking premiums to today. For some states the beginning year changes because we may have only recently operated in a state and thus I can't figure out how to do an all-time change based on the beginning year changing. 

For example, in Colorado, if last year the average limit anyone unit loss is $100,000 and in 2022 if the average limit anyone unit  $125,000; then the all time change would be 25%. Likewise, in Montana, in 2019 the average limit anyone unit loss was $100,000 and in 2022 the average limit anyone unit loss $150,000; then the all time change would be 50%.

So far have only managed to have a formula for the below:

As you can see, I need the _Baseline_Value to change based on when we first started taking in premiums. Btw, the State, the limit anyone unit, the premiums and the date are all on the same excel sheet together; so maybe I'm just being dumb!

Any advice/help would be greatly appreciated!

Best,

Luke

[v-chenwuz-msft]

Hi @LukeBrowneUK ,

So your goal is to get the first year of the state first.

var _firstyear = calculate( Year(min('Risk data'[Lasted updated date])), allexpect('Risk data','Risk data'[states]))

Then  _Baseline_Value

calculate( average('Risk data'[limite any one unit]) , 'Risk data'[Lasted updated date] = _firstyear)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.