cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Calculate cummulative value until condition

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:

4 REPLIES 4
Frequent Visitor

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])``

Community Support

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.

Frequent Visitor

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!

Frequent Visitor

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:

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors