Claim triangles in PowerBI

[M4r3]

Hello Community,

I am trying to build some claim triangles in PowerBI. I won't bore you with the details as I have one specific question for the matrix visual.

I have data in the following form:

I have already created a part of a matrix visual containing the cummulative amounts, and this works as it should:

I created a measure for this calculation:

Sum X Quarters = 
VAR xQuarters = SELECTEDVALUE('Time Columns Table'[Time difference])
VAR Result =
    CALCULATE(
        [Total sales],
        Triangle[TIME_UNITS_DELAY] <= xQuarters
    )
RETURN
    Result

The Time Columns Table contains the full range of column values:

Time Columns Table = 
SELECTCOLUMNS(
    {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},
    "Time difference", [Value]
)

The matrix visual values are the following:

For the 'triangle' to be correct I have the following things to do and spent a lot of time to no result:

1. The Rows of matrix visual should contain also the 'missing' quarters with no calculation shown. Does this mean that I should redesign the visual rows or is there another way of doing this?

2. The values should not be calculated after a specific time. For example the numbers marked red should not be calculated, so this thing would look like a triangle:

the calculation for not displaying is like (TIME_CLAIM_OCCURED + TIME_UNITS_DELAY (#quarters)) <= TIME_CLAIM_OCCURED in matrix row.

I tried with DATEADD (but not calculating where dates not contained in table) and I tried specifiyng directy in the RETURN, but I am stuck.

Thank you so much!

[Vmpbi]

Thanks for sharing… check out the link below. It provides a comprehensive guide on how to construct insurance triangles in Power BI.

https://insurancedatainsights.blog/2024/02/16/insurance-triangles-in-power-bi/

[vm_pbi]

Hi, the following blog post might help 
https://insurancedatainsights.blog/2024/02/16/insurance-triangles-in-power-bi/

[v-mengzhu-msft]

Hi @M4r3 ,

I'm not sure but you can try this:

Sum X Quarters = 
VAR xQuarters = SELECTEDVALUE('Claim Development Quarters'[Claim Development])
VAR xDates = SELECTEDVALUE('Claim Occured Date'[Claim Occured Date])
VAR Result =
    CALCULATE(
        sum(Table[Total sales]),
        Triangle[TIME_UNITS_DELAY] <= xQuarters,
        Triangle[TIME_CLAIM_OCCURED] = xDates
    )
RETURN
    IF(selectedvalue('Table'[reporting date]<= xDates, Result,0)

Best regards,

Community Support Team Selina zhu

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

[M4r3]

Hi @v-mengzhu-msft  thank you for the effort. This added condition makes no change. In the time of your proposal I was writing a more clear comment for my issue: https://community.powerbi.com/t5/Desktop/Calculate-cummulative-value-until-condition/m-p/2715925 Hope this is more clear.

[M4r3]

To explain the 2nd point further. I have added the 'Reporting_date' column directly in my data. It is a sum of the 'Time_claim_occured' and 'Time_units_delay' where the delay is in quarters.

My matrix visual now looks like this:

And the measure calculation looks like:

Sum X Quarters = 
VAR xQuarters = SELECTEDVALUE('Claim Development Quarters'[Claim Development])
VAR xDates = SELECTEDVALUE('Claim Occured Date'[Claim Occured Date])
VAR Result =
    CALCULATE(
        [Total sales],
        Triangle[TIME_UNITS_DELAY] <= xQuarters,
        Triangle[TIME_CLAIM_OCCURED] = xDates
    )
RETURN
    IF(Result = 0, 0, Result)

How can I write in the measure the IF statement or something similar? It should 'calculate until' the 'reporting_date is <= to the xDates. I tried including this in the CALCULATE, but it returns weird results.

[M4r3]

I solved the 1st point above by creating a similar table to the 'Times Columns Table" above and then adding it to the measure calculation.

Sum X Quarters = 
VAR xQuarters = SELECTEDVALUE('Claim Development Quarters'[Claim Development])
VAR xDates = SELECTEDVALUE('Claim Occured Date'[Claim Occured Date])
VAR Result =
    CALCULATE(
        [Total sales],
        Triangle[TIME_UNITS_DELAY] <= xQuarters,
        Triangle[TIME_CLAIM_OCCURED] = xDates
    )
RETURN
    Result

 For the 2nd point some help would be grately appreciated 🙂