Active Customers if has orders in last X months

[SergioTorrinha]

Hi all!

In my current data model, which is similar to one used my Marco Russo here , I would like to calculate te number of Actual active cusomters in a given month. I read all the content in link provided, but so far I wasn't able to find a way to build a virtual table from where it is possible to do the necessary calculations for my specific case.

This measure is to be used in a chart (x axis with a Year/Month column from dimCalendar), and needs to calculate the number of active customers in the selected month.
Active customers are the ones who ordered a product in the last 4 months of the selected Year/month.

Example:
1) In June 2020, the number of active customers are given by the count of customers that have at least one order done in the 4 previous months(February, March, April, May 2020).
2) In January 2020 (this is the minimal date period with data in facOrders table) Active customers will be equal to new customers (we can also apply the 4 months rule in this case, but i belive it wont be possible to propagate filters from dimCalendar to facOrders table, if at all necessary).

Can someone please recomend me a way of working this out?
I'm sorry I can't share any data as it is sensitive.
Thanks in advance. 🙂
Thanks in advance

[SergioTorrinha · Accepted solution]

Hi all,

I managed to solve the issue in the meanwhile.
I'm sharing, below, the code I used in case anyone needs in future.

Actual Customers =
VAR SelParameter =
SELECTEDVALUE ( StopParameter[Stop Months] )
VAR MinDate =
MIN ( dimDates[Date] )
VAR CustomersWithOrderDate =
CALCULATETABLE (
-- Prepares a table that
SUMMARIZE (
-- for each customer contains
facOrder,
-- all their orders
facOrder[CustomerID],
facOrder[OrderDate]
),
ALLSELECTED ( dimCustomer ),
-- Regardless of local filters on customer
ALLEXCEPT (
dimDates,
dimDates[Date]
) -- and on Date
)
VAR ExistingCustomers =
FILTER (
CustomersWithNewDate,
[OrderDate]
> EOMONTH ( MinDate, - SelParameter - 1 )
&& [OrderDate] <= MinDate -- done in the last x months from current period
)
VAR Result =
COUNTROWS ( ReturningCustomers )
RETURN
Result

Thank you. 🙂

[SergioTorrinha]

I'm in the process of trying to insert here some sample data, but seems like HTML doesen't like me very much! 🙂
I'll let u know as soon as I can load somehting here.