Calculate cummulative value until condition

[M4r3]

Hello community!

I am trying to calculate some cummmulative values in DAX, but would like to stop the calculation after a certain condition. My measure looks like this:

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

And the following code then introduces the appropriate filter context:

Triangle[TIME_UNITS_DELAY] <= xQuarters,

The matrix visual looks like this:

And I would wish that the red marked numbers would not be displayed. So the matrix would look like a triangle. Therefore these are the numbers where: (this is not 100% correct as there not always data in TIME_UNITS_DELAY).

Triangle[TIME_UNITS_DELAY] > xQuarters

The more appropriate condition would be 'stop where':

('Claim Occured Date'[Claim Occured Date] + 'Claim Development Quarters'[Claim Development] (quarter delay)) > MAX('Claim Occured Date'[Claim Occured Date])

But how to sum up the date plus the quarted delay (value)?

The original data looks like:

 

[M4r3]

For the summing up quarters I used the EDATE and it works as it should:

IF(EDATE(xDates,((xQuarters - 1) * 3) ) < DATE(2022,6,30) , Result, 0))

Now I need only the solution for my MAX 🙂

MAX('Claim Development Quarters'[Claim Development])

[v-chenwuz-msft]

Hi @M4r3 ,

Sorry, don't quite understand what you need, please provide some example data and please enter the results you want manually in excel.

Best Regards

Community Support Team _ chenwu zhu

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

[M4r3]

Sure, thank you for the effort. I am attaching the .pbix and Excel files. https://we.tl/t-aGjicVIRSK

Basically the only thing I would still like to do is to get the DATE(2022,6,30) fixed value in the measure 'Sum X Quarters' replaced with the MAX date from the [Claim dvelopment] table.

Thank you!

[M4r3]

I have solved the issue, but it is more static than I would like it to be. I still have two problems:

Sum X Quarters = 
VAR xQuarters = SELECTEDVALUE('Claim Development Quarters'[Claim Development])
VAR xDates = SELECTEDVALUE('Claim Occured Date'[Claim Occured Date])
VAR Result =
    CALCULATE(
        [Total claims],
        Triangle[TIME_UNITS_DELAY] <= xQuarters,
        Triangle[TIME_CLAIM_OCCURED] = xDates
    )
RETURN
    IF(xDates + ((xQuarters + 1) * 90 ) < DATE(2022,6,30) , Result, 0)

I would like to add the number of quarters more dinamically (or correctly), replacing the expression:

((xQuarters + 1) * 90 )

And would like to have the MAX date instead of:

DATE(2022,6,30)

If I replace this expression with:

MAX('Claim Development Quarters'[Claim Development])

This happens:

I tried the FORMAT, but received no good results. Probably also due to my missunderstanding. The end result with 'static' code looks like: