cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors