cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Improve performance of Cumulative formula

AnuTomy_0-1603615438399.png

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

https://drive.google.com/file/d/1C4cKXalkDq95yFt5BWdVwwoxL0oAiJ8U/view?usp=sharing 

 

 

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

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 =
    ADDCOLUMNS (
        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 (
                        SUM ( 'Unapplied Receipts'[AdvPayment] )
                    )
    )
RETURN
    SUMX (
        FILTER (
            summary,
            [@result] > 0
        ),
        [@result]
    )

 

Regards,

Pat





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

Proud to be a Super User!




View solution in original post

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

AnuTomy_0-1603621547789.png

 

This formula is giving incorrect results

Highlighted
Helper I
Helper I

Highlighted

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

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

Highlighted

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 =
    ADDCOLUMNS (
        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 (
                        SUM ( 'Unapplied Receipts'[AdvPayment] )
                    )
    )
RETURN
    SUMX (
        FILTER (
            summary,
            [@result] > 0
        ),
        [@result]
    )

 

Regards,

Pat





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

Proud to be a Super User!




View solution in original post

Highlighted

Could you please kindly explain why this formula is performing well

Highlighted

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 

SU18_powerbi_badge

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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