Cumulative over cumulative performance

[Antmkjr]

https://drive.google.com/file/d/1GznvuGASdWCxpaIl3MAjtnMoJmmj6HPB/view?usp=sharing

In the file attached, the formula for Cumulative Final To Collect Result is working fine, but in the actual model the performance is very bad. Any ways to improve ?

Cumulative Final To Collect Result =

CALCULATE( [Cumulative Final To Collect Prep],

FILTER( ALLSELECTED('Date'),

'Date'[Date] <= MAX( 'Date'[Date] )

)

)

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

[PaulPalma]

@Antmkjr 

I recommend reviewing this resource: Cumulative total – DAX Patterns

Based on your example I believe the main performance issue is related to iterations. You are creating a variable table in your DAX expression to create a Date Table for iterations. I recommend to use the power of the relationships with the Data Table to drive your DAX computations.

Example "Cumulative Final To Collect Prep" can most likely reduced to 

Cumulative Final To Collect Prep =

[Final To Collect]+[Total Project Collections]

All the best,

Paul

[lbendlin]

What are the filters that influence the result of the computation? Could you get away with using a calculated column instead of a measure? At least for part of the formula?