Optimizing measure - Segment migration

mwegener  (and everyone else)

Allow me to challenge you a bit further. See attached file for support.

We now want to calculate the revenue (sales) that the new customers that moved to the segment have generated. For that we have added an extra column (Table1[Sales]). We then have an initial approach, similar to [Net new customers]. Very slow as well:

Net new customers purchases = 
VAR _CurrentDate = SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate = CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
VAR _CustomerIDsThisMonth = DISTINCT ( Table1[CustomerId] )
VAR _CustomerIDsPreviousMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] = _PreviousDate
    )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        EXCEPT ( _CustomerIDsThisMonth, _CustomerIDsPreviousMonth )
    )

Following an approach similar to mwegener 's V2 (although it cannot be followed completely in this case), we can make it twice as fast, but still relatively slow:

Net new customers purchases V2 = 
VAR _CurrentDate = SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate = CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
VAR _CustomerIDsThisMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] IN  {_PreviousDate , _CurrentDate}
    )
VAR _CustomerIDsPreviousMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] = _PreviousDate
    )
RETURN
    CALCULATE ( SUM ( Table1[Sales] ), _CustomerIDsThisMonth )
        - CALCULATE ( SUM ( Table1[Sales] ), _CustomerIDsPreviousMonth )

Can you make it faster?

Many thanks