Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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