Forum Discussion
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
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:
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])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.
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!
