Trying to calculate loss ratio but not working

[Jazz_MT]

Hi All,

Hope you can shed some light on what I am doing wrong as I am using PowerBI for my Masters Project.

I am trying to calculate the loss ratio which has the formula (Total Claims incurred to Date/Total Premiums earned to date)*100. 

I have calculated both the Total Claims Incurred and Total Premiums Earned using the Year-to-Date Time Intelligence quick measure function. The value of 2017 is correct however the values of 2016 and 2015 are incorrect and also when the graph is plotted as shown in the attached image below. Whats strange is the Graphs plotting the Total Claims incurred and Total Premiums incurred is correct and I was assuming this would be a simple division.

Really hope someone can help me with this as it would be much appreciated 🙂 

Thanks

[Interkoubess]

Hi @Jazz_MT,

Do you have a calendar table ?

please could you show a sample of your data ( how it is stored) and will try to help.

Thx.

[Jazz_MT]

Hi @Interkoubess

I do not have a calendar table and below are screenshots of my data model including the tables. Hope you can help me on this. If you need further details maybe we could skype ? 

Really appreciate your help

Data modelClaims 1Claims 2Earned PremiumPolicy HeaderPolicy Lines

[v-jiascu-msft]

Hi @Jazz_MT,

Could you please post your data in the text mode due to you can share your data? Sharing a PBIX file would be great if it's convenient for you. Dummy data is enough. Maybe you could check these things below first.

1. It's highly recommended using time intelligence functions with a date table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] )
)

2. Year-to-date functions accumulate the result. Is this what you want?

3. Could you please post the formulas you have created?

Best Regards!

Dale