Improve performance of Cumulative formula

[Antmkjr]

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] ) ))

[Antmkjr]

@Greg_Deckler @amitchandak 

[colacan]

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

[Antmkjr]

This formula is giving incorrect results

[colacan]

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,

[Antmkjr]

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

[mahoneypat · Accepted solution]

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

[Antmkjr]

Could you please kindly explain why this formula is performing well

[AlB]

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