Distinct Count of Active Customers in the trailing 12 months for each month

[Anonymous]

This may be a fun challenge to you superstars (see limitation at the end): 

I need to show Distinct Active Customers for 12 months by month (Rolling 12 month active customer count).

So I need a measure that will DISTINCTCOUNT CustomerID by < the time period > where Revenue for the CustomerID is not null in that <timeperiod>

LIMITATION: We have to use a DataCube that does not allow Columns or Tables. We can only make Measures

The limitation is something I cannot get around otherwise I can do this.

Here is a sample and the data fields:

SAMPLE OUTPUT	201801	201802	201803	201804	201805	201806	201807	201808
Distinct Active Customers	820	900	960	1,004	1,111	1,140	1,130	1,125
____________________________
Table	Customer	_Sales	DateTable
Field	Customer ID	Revenue	CalendarDate
Type	Text	Number	Date

[v-juanli-msft]

Hi @Anonymous

Are the key columns and relationship among tables like below?

Best Regards

maggie

[Anonymous]

Indeed they are Maggie.