Rolling 12 month distinct count

I trying to create a data set which is distinct count of customers over a rolling 12 month period, which increments by a month but don't really know where to start so any help would be greatly appreciated. What my desired output would look something like the table below. Have attached a example input data set

Start Period	end period	distinct count
May 2018	Apr 2019	33
Jun 2018	May 2019	31
Jul 2018	Jun 2019	32

Sample input.xlsx

Common Use Cases

Time Series

Accepted answers

Aaron_Harter

@jboyle one approach to solving this problem might involve summarize tools to perform the customer counts, and the generate rows tool with DateTime functions to create the data points required for your analysis:

2020-03-17 data prep sample.yxmd

All comments

Aaron_Harter

2020-03-17 data prep sample.yxmd

CharlieS

Hi @jboyle

Thanks for that example, it was really helpful.

I've attached an example solution. Check it out and let me know what you think.

20200317-Rolling12Unique.yxmd

jboyle

Thanks guys both of these solutions worked 🙂

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703