Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Antmkjr
Helper V
Helper V

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

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
Antmkjr
Helper V
Helper V

colacan
Resolver II
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

AnuTomy_0-1603621547789.png

 

This formula is giving incorrect results

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,

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

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Could you please kindly explain why this formula is performing well

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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors