calculate using sum and filter

Hi jpbi23 ,

I am glad to help you.
According to your description, I suggest you can create a virtual table in measure as an intermediate amount to increase the execution efficiency of dax, the modified code is as follows:

 

Remaining Months Recurring Services Revenue =
VAR SelectedDate =
    SELECTEDVALUE ( 'Date'[PERIOD] )
VAR EndDate =
    EDATE ( SelectedDate, 60 )
VAR table_ =
    FILTER (
        MASTER_REVENUE,
        MASTER_REVENUE[PERIOD] > EndDate - MASTER_REVENUE[On hold Recurring Service]
    )
RETURN
    CALCULATE (
        SUM ( MASTER_REVENUE[AMOUNT] ),
        FILTER ( table_, table_[REVENUE_CATEGORY] IN { "Recurring Services" } )
    )

 

For the optimization of the above code, I made test data and tested the performance of your previous dax code and the optimized dax code using virtual tables

Here is my test data (with 500 rows):

Write using virtual tables:

 

M_1 = 
VAR table_ =
    FILTER (
        Test_,
        Test_[saleTime] <=NOW()
    )
RETURN
    CALCULATE (
        SUM ( Test_[amount] ),
        FILTER ( table_, Test_[fruitName] IN { "apple" } )
    )

 

Virtual table writes are not used:

 

M_2 = 
    CALCULATE (
        SUM ( Test_[amount] ),
        FILTER ( Test_, Test_[fruitName] IN { "apple" } &&  Test_[saleTime] <=NOW() )
    )

 

I used each of these two MEASURES six times

The top six times are M_1

The bottom six times is M_2

The test results are as follows:

 

The average execution time for M_1 = 123ms
The average execution time for M_2 = 156ms

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.