topic Re: Improve performance of Cumulative formula in DAX Commands and Tips

Improve performance of Cumulative formula

Anonymous — Sun, 25 Oct 2020 08:48:16 GMT

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

Re: Improve performance of Cumulative formula

colacan — Sun, 25 Oct 2020 09:37:14 GMT

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

Re: Improve performance of Cumulative formula

Anonymous — Sun, 25 Oct 2020 10:26:42 GMT

This formula is giving incorrect results

Re: Improve performance of Cumulative formula

Anonymous — Sun, 25 Oct 2020 12:18:31 GMT

Greg_Deckler amitchandak

Re: Improve performance of Cumulative formula

colacan — Sun, 25 Oct 2020 12:30:10 GMT

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,

Re: Improve performance of Cumulative formula

Anonymous — Sun, 25 Oct 2020 12:33:25 GMT

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

Re: Improve performance of Cumulative formula

mahoneypat — Sun, 25 Oct 2020 15:04:19 GMT

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

Re: Improve performance of Cumulative formula

Anonymous — Mon, 26 Oct 2020 05:17:49 GMT

Could you please kindly explain why this formula is performing well

Re: Improve performance of Cumulative formula

AlB — Mon, 26 Oct 2020 16:59:26 GMT

Hi Anonymous

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