Moving average measure with slow performance

[738o51]

Hi community,

I have built a moving average measure but it is having some performance issues (takes 1-2 minutes to load or to adjust when I change filters). I've already edited my data model to make it as efficient as possible. It seems like the rest of the report is working smoothly, but this measure is being particularly slow. Can anyone suggest a more efficient way to build it?

My measure is trying to estimate the number of customers who are returning to a retail location within 30 days since their last visit and who are purchasing something.

Here is the rolling 7 average that is having performance issues:

Rolling 7 day unique return customers making purchase = 
var sumreturnpurchaseslast7 = 
CALCULATE(
    [Unique return customers making purchase],
    DATESINPERIOD(
        'Date Dimension'[Date],
        LASTDATE('Date Dimension'[Date]),
        -7,
        DAY))
RETURN
divide(sumreturnpurchaseslast7,7)

Here is the measure it is based on:

Unique return customers making purchase:
CALCULATE(
    DISTINCTCOUNT('Customer visit fact'[ID]),
    'Customer visit fact'[Purchase status]="Purchase made",
    'Customer visit fact'[Visit is 30 days or less from previous]=1)

 The date dimension table has a one to many relationship with the purchase date field in the Customer Visit fact table.

Any help would be greatly appreciated!

Thanks,

738051

[v-luwang-msft]

Hi @738o51 ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft]

Hi @738o51 ，

So far, It don't see any problems with your Dax (it seems to me that it can no longer be optimized), is it that the amount of data in the table is so large that it takes longer to calculate the rolling 7-day calculation?

Best Regards

Lucien

[AlexisOlson]

You could try SUMX ( DISTINCT ( 'Customer visit fact'[ID] ), 1 ) instead of DISTINCTCOUNT('Customer visit fact'[ID]) as discussed here. Might not help at all but it's worth a try.