SUMMARAZE performance / early materialization

[AlexOakman]

Hi, I have a large fact table. I need to calculate a tricky measure. The first step I need to group this table by a certain set of attributes, excluding repetitions, and then find the sum over the resulting table. I use this measure in detailing cumulative dates by month. 
An example of dax:

'📊Measure'[TEST_v1] =

VAR _min = SELECTEDVALUE('Dim date'[firstdate_of_month_key],99999999)
VAR _max = MAX('Dim date'[date_key])

VAR _tbl2 =
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE( 'Test_balance', KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Dim date')),
'bridge_dpd_addition'[dpd_group_company],
'Test_balance'[prolongation_num_cumulative],
'Test_balance'[amount_usd]
),
"cnt", CALCULATE(DISTINCTCOUNT('Test_balance'[credit_key] ),KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Dim date'))
)

VAR _res = SUMX(_tbl2,[amount_usd]*[cnt])

RETURN
_res
-------------------------
or _v2 instead of a _tbl2 I can use 

VAR _tbl1 = SUMMARIZE(    
    CALCULATETABLE( 'Test_balance', KEEPFILTERS('Test_balance'[date_key]>=_min&&'Test_balance'[date_key]<=_max),REMOVEFILTERS('Test_balance')),    
    'bridge_dpd_addition'[dpd_group_company],
    'Test_balance'[prolongation_num_cumulative],
    'Test_balance'[amount_usd],
    'Test_balance'[credit_key] 
    )
VAR _res =   SUMX(_tbl1,[amount_usd])
But in both cases I have early materialization of the data. Is there any way to avoid this?

[AlexOakman]

Solved by creating additional columns with a sign 1/0 and a simple Calculate (sum(),flag=1)

[tamerj1]

@AlexOakman 

You may also try

TEST_v1 =
SUMX (
VALUES ( 'Dim date'[firstdate_of_month_key] ),
VAR _min = 'Dim date'[firstdate_of_month_key]
VAR _max =
CALCULATE ( MAX ( 'Dim date'[date_key] ) )
VAR _tbl1 =
FILTER (
CALCULATETABLE ( 'Test_balance', ALL ( 'Test_balance' ) ),
'Test_balance'[date_key] >= _min
&& 'Test_balance'[date_key] <= _max
)
VAR _tbl2 =
SUMMARIZE (
_tbl1,
'bridge_dpd_addition'[dpd_group_company],
'Test_balance'[prolongation_num_cumulative],
'Test_balance'[amount_usd],
'Test_balance'[credit_key]
)
VAR _res =
SUMX ( _tbl2, [amount_usd] )
RETURN
_res
)

[AlexOakman]

Thanks for the advice. Unfortunately the result is the same as for ADDCOLUMN(SUMMARAZE(...)) =(

[johnt75]

If you have a relationship from your date table to the fact table then there is no need to apply the date filters to your fact table and you could simplify the code to

TEST_v1 =
VAR _tbl2 =
    ADDCOLUMNS (
        SUMMARIZE (
            'Test_balance',
            'bridge_dpd_addition'[dpd_group_company],
            'Test_balance'[prolongation_num_cumulative],
            'Test_balance'[amount_usd]
        ),
        "cnt", CALCULATE ( DISTINCTCOUNT ( 'Test_balance'[credit_key] ) )
    )
VAR _res =
    SUMX ( _tbl2, [amount_usd] * [cnt] )
RETURN
    _res

[AlexOakman]

I need to calculate cumulative values for the month by day. Something like that: