0

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

RBreuer
  • 1,371
  • 1
  • 7
  • 17
Serdia
  • 4,242
  • 22
  • 86
  • 159

1 Answers1

3

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.

TJ_
  • 629
  • 6
  • 12
  • Thanks TJ_ I just converted 'Losses'[LossDate]' to date, but unfortunately it still displays data the same way. – Serdia Apr 03 '18 at 19:14
  • Are you sure you've converted the datatype, and not just changed the display format? Could you try to create a new column, with this DAX: LossDateLink = INT('Losses'[LossDate]) – TJ_ Apr 03 '18 at 19:17
  • Oh, sorry, you was right. I changed it in Query Editor and its completely different now. I'll update question with link what I am trying to achieve. – Serdia Apr 03 '18 at 19:21
  • @Oleg, I've checked the links, but could you explain in your own words what you want to see, based on your measures and your model? Otherwise an insurance expert has to take a look at it. – TJ_ Apr 03 '18 at 19:36