cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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

Accepted Solutions
Highlighted
Super User III

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

Proud to be a Super User!

8 REPLIES 8
Highlighted
Helper 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

Highlighted
Helper I

This formula is giving incorrect results

Highlighted
Helper I
Highlighted
Helper 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,

Highlighted
Helper I

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

Highlighted
Super User III

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

Proud to be a Super User!

Highlighted
Helper I

Could you please kindly explain why this formula is performing well

Highlighted
Super User II

Hi @AnuTomy

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors