How to create Actuarial Loss Triangle in Power BI Desktop?

0
votes

I am trying to create Loss Triangle matrix. Examples are here: https://community.powerbi.com/t5/Desktop/Insurance-Loss-Triangles/td-p/197855

https://community.powerbi.com/t5/Desktop/Insurance-Earned-Premium-Loss-Ratio-Calculation/td-p/181060/page/2

Two tables Dim_dates and Losses joined by Date.

enter image description here

Data in two table looks like this:

enter image description here

So I'm using matrix, put LossMonthYear as Rows, MonthInCalendar as Columns, and Losses as Values. But why I only see Jul 2015 and Dec 2015 as columns? Do I need to do anything else in order to create Actuarial Loss Triangle?

enter image description here

.ipbx page 3 file can be accessed here: https://www.dropbox.com/s/dw5okx9ettm0fcl/Premium%20by%20Company.pbix?dl=0

1
powerbidaxpowerbi-datasource

1 Answers

3
votes

I'm not sure what you're trying to accomplish, but the reason why you don't see any data is because the relationship between 'Losses'[LossDate] and 'Dim_Dates'[Date] is wrong: one is a date column, the other a datetime column. (Not totally correct; LossDate is an actual datetime column, Date is a datetime column containing only a date: 12-05-2018 00:00:00).

Removing the time part, or create a new column with only the date, and use that one for the relationship should work. However, I'm not sure if the result is what you want to see. If not, could you post the expected outcome?

Added, after comment:

You can create a new column with this formula, if you wish to retain the original datetime:

NewDate = INT('Losses'[Date])

Or

NewDate = DATE(YEAR('Losses'[Date]),MONTH('Losses'[Date]),DAY('Losses'[Date]))

Or

You can change the datetype in the Query Editor, then you'll loose the time. Changing the type in the Data View will only change the Display Format, and will not have the desired effect.