Calculate Premium Forecast

[swtgoyal]

Hi,

I am struggling to create a visual to show premium for future dated policies. I need to calculate the future premium based on a formula ( coverage amount/1000) * rate factor only for policies having future anniversary date. 

FuturePremium =

VAR AgeRange = [Age Range]

VAR PLANID = [PlanId]

Return

SWITCH(

    TRUE(),

         CONTAINSSTRING(AgeRange, "Under 25") && PLANID IN {"933","922"}, [CoverageAmount]/1000) * 0.027, 

         CONTAINSSTRING(AgeRange, "25-29") && PLANID IN {"933","922"}, [CoverageAmount]/1000) * 0.028,

0

)

 I have calculated member age range as per their anniversary date using current age. For ex - a member is 23 now and has next year anniversary date so it will fall "under 25" and have future premium accordingly. this is only for active policies. 

I have a date table having all dates (future and past) which is tied to Policy issue date.

When i create a line chart using dates from date table and premium (actual and future) for the policies, it shows the data till 2023 year because policy issue dates are till 2023.. how to show the Future Annual premium for these policies. For example - right now, first record have Annual Premium of $50 but in year of 2025, it will go to $100 so when we see all the policies..it will tell how much premium we will getting for these policies in upcoming years and also show how much we have in 2023(when it was issued). Considering all remain active. 

Year	Month	Policy Anniversary date	future Member Age	Age Range	Annual Premium	Future Annual Premium
2023	January	5/22/2025	25	25-29	50	100
2023	Februrary	10/23/2029	33	30-34	100	150
2020	March	4/10/2026	24	Under 25	230	200
2021	January	4/10/2024	45	45-49	100	150
2017	Decemeber	4/10/2028	19	Under 25	200	230

[ChiragGarg2512]

Try using this measure:

FuturePremium =
VAR AgeRange = [Age Range]
VAR PLANID = [PlanId]
VAR PolicyAnniversaryYear = YEAR([Policy Issue Date]) + 1 
VAR CurrentYear = YEAR(TODAY()) 
VAR PremiumFactor =
    SWITCH(
        TRUE(),
        CONTAINSSTRING(AgeRange, "Under 25") && PLANID IN {"933", "922"}, 0.027,
        CONTAINSSTRING(AgeRange, "25-29") && PLANID IN {"933", "922"}, 0.028,
        0
    )
RETURN
    IF(PolicyAnniversaryYear > CurrentYear, ([CoverageAmount] / 1000) * PremiumFactor, 0)

[swtgoyal]

Thank you for responding!

Premium anniversary date is already calculated and another formula is in place - 

Forecasting Premium = IF([PolicyAnniversaryDate] > TODAY(), '[FuturePremium], [Annual Premium])

When i create a Line chart - what date to use with Forecasting premium? If i use Date(tied to policy issue date) it shows data till 2023 and if i use policy anniversary date then also it doesn't work.

How I visualize, Calendar Date - 2021, 2022, 2023, 2024 and so on.. it should show the Forecast premium of all polices and if a member has policy anniversery in 2024 it should Future premium so you will see a spike in premium in upcoming years.