Very slow measures

[Anonymous]

Hi all, 

I've been trying to get 2 measures to work more efficiently, and I can't seem to pin down what the problem comes from after hours analyzing it : 

ActiveMembers :=
CALCULATE (
    [IsMember],
    FILTER (
        VALUES ( 'Client'[Last order] ),
        LASTDATE ( Client[Last Order] )
            > LASTDATE ( 'Time'[Date] ) - 112
            && LASTDATE ( 'Client'[Last order] ) <= LASTDATE ( 'Time'[Date] )
    )
)

DormantMembers :=
IF (
    HASONEVALUE ( 'Client'[Last Order] ),
    CALCULATE (
        [IsMember],
        FILTER (
            ALL ( 'Time'[Week] ),
            LASTDATE ( Client[Last Order] )
                <= LASTDATE ( 'Time'[Date] ) - 112
        )
    ),
    CALCULATE (
        [IsMember],
        FILTER (
            VALUES ( Client[Last Order] ),
            LASTDATE ( Client[Last Order] )
                <= LASTDATE ( 'Time'[Date] ) - 112
        )
    )
)

For reference, IsMember is : 

IsMember :=
CALCULATE ( DISTINCTCOUNT ( [ID] ), Facts[Member] = 1 )

Do you have an idea what could be the problem here ? I'm trying to see if I can work upstream and calculate this with an SSIS query instead, but it'd be better to fix it from DAX directly.

Kindly,

Nossair

[amitchandak]

@Anonymous , if are creating a rolling 112 days formula then try like

Rolling 112 = CALCULATE([Is Member],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-112,day))

Can you explain what are you trying to achieve here

[Anonymous]

Hi amitchandak,

The first measures calculates the number of clients that have made a purchase in the last 16 weeks, and the second calculates the number of clients whose last purchase came before that.