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

Helper V

## Improve performance of Cumulative formula

The below formula seems to be working fine, But is very slow , can anyone suggest ways to improve.

Cumulative Net To Collect =

CALCULATE( SUMX (
SUMMARIZE (
'Date',
'Date'[MonthnYear],
"_1", [Net Cumulative To Collect]
),
[_1]
),FILTER ( ALLSELECTED( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ))
1 ACCEPTED SOLUTION
Employee

Here is a different way to write it that gets the same result.  Please check if its is more performant with your actual data.

New Measure =
VAR summary =
SUMMARIZE (
'Date',
'Date'[Year],
'Date'[MonthName],
"maxdate"MAX ( 'Date'[Date] )
),
"@result",
VAR maxdate = [maxdate]
RETURN
CALCULATE (
SUM ( 'To Collect'[To Collect] ),
ALLSELECTED ( 'Date'[Date] ),
'Date'[Date] <= maxdate
)
CALCULATE (
)
)
RETURN
SUMX (
FILTER (
summary,
[@result] > 0
),
[@result]
)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

8 REPLIES 8
Helper V
Resolver II

Please try and check if it is faster...

Cumulative Net To Collect  =

VAR CurrentDate = MAX ( Date[date] )

VAR CumulCollect = CALCULATE (
SUM ( Table[Net Cumulative To Collect] ),
FILTER ( ALL ( Table), Date[date] <= CurrentDate )
)
RETURN
CumulCollect

Helper V

This formula is giving incorrect results

Resolver II

When a measure refer to other measure, sometime unexpected result comes. Maybe trying it again with actual formula instead of [Net Cumulative To Collect] might work. I recomend to test the fomular with simple table first,

Helper V

I have attached the sample file in the link, could you please suggest , how to make it work

Employee

Here is a different way to write it that gets the same result.  Please check if its is more performant with your actual data.

New Measure =
VAR summary =
SUMMARIZE (
'Date',
'Date'[Year],
'Date'[MonthName],
"maxdate"MAX ( 'Date'[Date] )
),
"@result",
VAR maxdate = [maxdate]
RETURN
CALCULATE (
SUM ( 'To Collect'[To Collect] ),
ALLSELECTED ( 'Date'[Date] ),
'Date'[Date] <= maxdate
)
CALCULATE (
)
)
RETURN
SUMX (
FILTER (
summary,
[@result] > 0
),
[@result]
)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper V

Could you please kindly explain why this formula is performing well

Super User

Hi @Antmkjr

Are you sure your version of the measure is not performing well in terms of speed? I tried a few different options and measured them with the performance analyzer in Power BI and yours seems to be the fastest by quite a bit.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

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