Cumulative over cumulative issue

[Anu]

sample 29-12.pbix (191.9 KB)

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]

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]

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]

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]

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

What is this part doing AverageX?

[Melissa]

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]

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

[Melissa]

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]

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]

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