Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
M4r3
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:

Triangle example 3.png

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:

Added reporting date.png

4 REPLIES 4
M4r3
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])

 

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!

M4r3
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:

Triangle example 4.png

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

Triangle example 5.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors