cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
M4r3
Frequent Visitor

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:

Data PBI.png

 

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

Triangle example.png

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:

Visual values.png

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: Triangle example 2.png

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!

 

4 REPLIES 4
v-mengzhu-msft
Community Support
Community Support

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.

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
Frequent Visitor

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.Added reporting date.png

My matrix visual now looks like this:

Current table.png

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
Frequent Visitor

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 🙂

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors