Insurance triangle

[Anonymous]

 

I'm using a matrix to display a triangle.  The triangle is cumulative and currently extends to the maximum developmentperiodkey.

This is all well and good but I want it to blank /hide/not calculate any values which are greater than todays month (developmentperiodkey Rows) highlighted in RED based on the Year (Columns). but don't have the DAX skills to do it, not for the want of trying...

The code for the measure is currently:

Measure = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods

Any help would be greatly appreiated.

Many thanks. 

[Vmpbi]

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

[parry2k · Accepted solution]

Try this

Measure = CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),
DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods)))
*
DIVIDE([Value],[Value])

[Anonymous]

Incredible... thank you so much for the solution and amazingly quick response!!??

You don't know how long I'm spent looking at this.

Very much obliged.

[parry2k]

@AnonymousI'm glad it worked out, I love PowerBI community. Feel free to reach out for any future questions. Cheers!!!

[Anonymous]

Thanks again for the answer, however since nothing is straight forward I have a slight problem with the solution.

Below is a slightly different example, where the value (In Orange) has not changed in a period so the Cumulative Values stays the same.

These values are now (on the right) understandably blank, is there any way to prevent this from happening until we reach the Current Months Development period?

Many thanks in advance

[parry2k]

Please use relative date filter and remove divide calculation with total

[Anonymous]

Thanks, tried that, but now values contine to end of development periods as before:

Running Total = 
CALCULATE([Value],FILTER(ALL('DevelopmentPeriods'),DevelopmentPeriods[DevelopmentPeriodKey]<=MAX(DevelopmentPeriods[DevelopmentPeriodKey])))

Is there a way to restrict what they develop to by a dynamic date.

I believe The ALL() Filter set this to all periods?

Thanks again!

[parry2k]

Can you share the screen shot what you put in filter for relative date filter?

[Anonymous]

I'm trying to simplify things with the example data.

The Relative filter I've just created uses the Accounting Period Start Date.

This is because there are no dates in the development periods table.

Each Value havs a development period derived from the Accounting period of the transaction:

Thank you 

[parry2k]

@Anonymoussorry Im little lost here, I guess it will make sense if you can share sample data and how your tables are related.

[Anonymous]

Completely understand, I'm not making it very clear.

Its quite complex, I'm also using SSAS tab as the data source.

I'll see what I can do to Clarify.

Thanks again.

[parry2k]

or you can add a date filer using relative filtering.