Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Claim triangles in PowerBI

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!

 

Need Help
Show and Tell

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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.