Rolling average of a measure using DISTINCTCOUNT

[Anonymous]

Hi all,

So I have a typical sales data table with columns containing Date, CustomerName, ProductCode, Qty, Price etc.

I am analysing the number of unique customers ordering from us per week, and have a measure using:

DistinctCustomers = DISTINCTCOUNT(CustomerName)

which I have plotted over time (see below) and this works fine.  

However I also want to include a 13 week (91 day) rolling average to smooth out the peaks and troughs and assess the general trend. I tried the usual

CALCULATE(DistinctCustomers, DATESINPERIOD(Date, LASTDATE(Date), -91, DAY)

Divided by 13 to get the 13 week average. However the above seems to calculate the number of unique customers within the 13 week period, not the sum of the unique customers in each week. I guess I could create a measure containing 13 individual CALCULATE statements, one for each week of the rolling average, sum them and divide by 13 but I can't believe that is the most elegant DAX solution.

Anyone out there already have a solution to rolling average of a DISTINCTCOUNT measure? Any help or suggestions gratefully received.

Cheers,

Michael.

[TomMartens · Accepted solution]

Hey,

Maybe this is what you are looking for:

SUMX(
    DATESINPERIOD(Date, LASTDATE(Date), -91, DAY)
    ,[DistinctCustomers]
)
/13 

Hopefully this provides what you are looking for.

If not - you might consider to provide an Excel file with some sample data, upload the file to onedrive or dropbox and share the link. This provides the possiblity to make up your expected result and also avoids misunderstandings.

Regards,

Tom

[Anonymous]

Your a genius, many thanks Tom, that is working perfectly.