Cumulative over cumulative issue

Anu · December 29, 2020, 12:07pm

In the attached file , I want Cumulative Final to Collect to be the cumulative of Total Project Collections and Final To Collect

For Ex: In 2020 Dec,
Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 = 20+0= 20

In 2021 Jan,
Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 +Final To Collect for Jan 2021 = 20+0+10 = 30
In 2021 Feb,
Cumulative Final To Collect = Total Project Collections+ Final To Collect for Dec 2020 +Final To Collect for Jan 2021+Final To Collect for Feb 2021= 20+0+10+3 = 33

But in the current formula is not working.

Please help?

Melissa · December 29, 2020, 1:48pm

Hi @Anu,

Give this a go.

Cumulative Final To Collect Prep = 
VAR vTable = 
    ADDCOLUMNS( 
        SUMMARIZE( 'Date', 'Date'[Year], 'Date'[MonthName] ),
        "@Total", [Final To Collect]+[Total Project Collections]
    )
RETURN

    SUMX( vTable, [@Total] )

and for the final result.

Cumulative Final To Collect Result = 
CALCULATE( [Cumulative Final To Collect Prep],
    FILTER( ALLSELECTED('Date'),
        'Date'[Date] <= MAX( 'Date'[Date] )
    )
)

I hope this is helpful.

Anu · December 30, 2020, 4:58am

The formula seems to be working fine, but in the actual model the performance is not so good .Anyway to improve the performance of the above formula?

Melissa · December 30, 2020, 12:41pm

Hi @Anu,

Give this a go.

Cumulative Final To Collect Result v2 = 
VAR vTable =
ADDCOLUMNS(    
    ADDCOLUMNS( 
        CALCULATETABLE(
            GENERATE( VALUES('Date'[Year] ), VALUES('Date'[MonthName] )),
            KEEPFILTERS( 'Date' )
        ),
        "@MaxDate", MAX( 'Date'[Date] )
    ),
        "@ToCollect",
            VAR MaxDate = [@MaxDate] RETURN
            CALCULATE( SUM( 'To Collect'[To Collect] ),
                ALLSELECTED( 'Date'[Date] ),
                'Date'[Date] <= MaxDate
            ) - [Unapplied Receipts],
        "@ProjColl",
            VAR MaxDate = MAX( 'Date'[Date] ) RETURN
            CALCULATE( SUM( 'To Collect'[Collections] ),
                ALLSELECTED( 'Date'[Date] ),
                'Date'[Date] <= MaxDate
            )
    )
VAR Result =
    AVERAGEX( FILTER( vTable, [@ToCollect] >0 ),  [@ToCollect] ) +
    AVERAGEX( vTable, [@ProjColl] )

RETURN Result

I have no doubt our local DAX optimization wizard @AntrikshSharma can improve this further but let’s see how you get on with it first.

Anu · December 30, 2020, 1:16pm

AVERAGEX( FILTER( vTable, [@ToCollect] >0 ), [@ToCollect] ) +
AVERAGEX( vTable, [@ProjColl] )

What is this part doing AverageX?

Melissa · December 30, 2020, 1:24pm

In context of your table visual it’s returning exactly the same result as SUMX would do over the vTable, except for the Total Row where SUMX will return an incorrect value. To correct that I used AVERAGEX instead, to illustrate why that works I’ve added an image below showing what each AVARAGEX is returning in the Measure. I hope that clarifies it better.

Anu · December 30, 2020, 1:41pm

Would you be so kind to explain why this formula is better over the other?

Melissa · December 30, 2020, 1:42pm

Hi @Anu,

So here’s another solution provided by @AntrikshSharma performance wise equal to the last one.
I hope this is helpful

Antriksh = 
VAR MaxMonthYear =
    MAX ( 'Date'[MonthnYear] )
VAR vTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            DISTINCT ( 'Date'[MonthnYear] ),
            "@Total", [Final To Collect] + [Total Project Collections]
        ),
        ALLSELECTED ( 'Date' )
    )
RETURN
    SUMX ( FILTER ( vTable, 'Date'[MonthnYear] <= MaxMonthYear ), [@Total] )

Anu · December 31, 2020, 7:42am

Thanks!

I can see the solution you had given first time is some what similar.
The performance improvement in Antriksh formula ,is it because he is not using Summarize/filtered the dates to only required dates?

VAR vTable =
ADDCOLUMNS(
SUMMARIZE( ‘Date’, ‘Date’[Year], ‘Date’[MonthName] ),
“@Total”,
[Final To Collect]+[Total Project Collections]
)
RETURN

SUMX( vTable, [@Total] )

AntrikshSharma · January 3, 2021, 9:35am

This one is 4.5x faster and runs in ~16ms compared to previous one which runs in ~74ms. Since DAX relies on Cache, after the second refresh of the visual it will start running in ~12ms.

Antriksh 2 = 
VAR MaxMonthYear =
    MAX ( 'Date'[MonthnYear] )
VAR vTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            DISTINCT ( 'Date'[MonthnYear] ),
            "@Total",
                CALCULATE (
                    MIN (
                        SUM ( 'To Collect'[To Collect] ),
                        CALCULATE (
                            VAR MaxDate =
                                MAX ( 'Date'[MonthnYear] )
                            VAR Result =
                                CALCULATE (
                                    SUM ( 'To Collect'[To Collect] ),
                                    'Date'[MonthnYear] <= MaxDate,
                                    REMOVEFILTERS ( 'Date' )
                                )
                                    - CALCULATE ( SUM ( 'Unapplied Receipts'[AdvPayment] ), REMOVEFILTERS ( 'Date' ) )
                            RETURN
                                Result,
                            FILTER (
                                ALL ( 'Date'[MonthnYear] ),
                                'Date'[MonthnYear] <= MAX ( 'Date'[MonthnYear] )
                            )
                        )
                    )
                )
                    + CALCULATE ( SUM ( 'To Collect'[Collections] ) )
        ),
        ALLSELECTED ( 'Date' )
    )
RETURN
    SUMX ( FILTER ( vTable, 'Date'[MonthnYear] <= MaxMonthYear ), [@Total] )