Monthly Active Clients

[Anonymous]

Hello all,

I need to count the active clients of each month. A client is considered active when he trades. There are two dates. Open trade date and Close trade date. The main problem is between those dates. 

For example a client opened a trade on 01/09/2019 and close it on 31/12/2018. It is easy for me to say that the client was active in September and in December. How can I count him as active in October and November also? 

Thank you in advance.

[Anonymous]

I prepair an excel file and I need ideas for translation to dax, as the mentioned solutions didn't help me. 

Below are my data. 

 

I create the below table by adding Months and I used formula with result 1 my client was active and 0 if wasn't during the month. 

Formula: =IF(AND((EOMONTH(D$1,0)>=$B2),(D$1<EOMONTH($C2,0))),1,0)

Then I summarize the table per client. 

=IF(SUMIFS(Data!D$1:D$999,Data!$A$1:$A$999,Results!$A2)=0,0,1)

[Anonymous · Accepted solution]

In continuation of the above, the below is the solution I found. 

Active Clients # =

var minDate = MIN(DateTable[Date])

var maxDate = Max(DateTable[Date])

return

CALCULATE(DISTINCTCOUNT('test_TRADES'[MT4 ID]),

FILTER('test_TRADES',

OR(OR(OR(

'test_TRADES'[OPEN_TIME]>=minDate && 'test_TRADES'[OPEN_TIME]<maxDate+1,

'test_TRADES'[OPEN_TIME]<minDate && 'test_TRADES'[CLOSE_TIME]>maxDate+1),

'test_TRADES'[CLOSE_TIME]>=minDate && 'test_TRADES'[CLOSE_TIME]<maxDate+1),

'test_TRADES'[OPEN_TIME]<minDate && 'test_TRADES'[CLOSE_TIME]=DATE(1970,01,01))))

The above check four possibilities. Let's say I want the active clients for December 2018. 

1. checks whether a client open a trade during December 2018

2. chekcs whether a client open a trade prior to December 2018 AND close it after December 2018.

3. checks whether a client close a trade during December 2018

4. chekcs whether a client open a trade prior to December 2018 AND it is still open. 

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Stachu]

the dates should be disconnected from your Calendar table (no joins)
this measure will work then if you put both the customer, year and month in the visual as well

Customer Status =
VAR __CurrentDate = MAX(Calendar[Date])
VAR __CustOpen = MIN('Table'[Open trade date])
VAR __CustClose = MAX('Table'[Close trade date])
RETURN
IF( __CurrentDate >= __CustOpen && __CurrentDate <= __CustClose, "Active", BLANK())

 EDIT only now noticed that you need total count - that would be something like this (also no joins)

Measure = 
VAR __CurrentMonth = MAX('Calendar'[Date])
VAR __ActiveCustomers = FILTER('Table',__CurrentMonth >= 'Table'[Open trade date] && __CurrentMonth<= 'Table'[Close trade date])
RETURN
COUNTROWS(__ActiveCustomers)

[Anonymous]

Hello Sir,

I forgot to say that 1 client has multiple trades. I can't take min and max because a client may open a trade on 01/01/2018 close on 01/03/2018 and then open another trade on 01/06/2018 and close it on 01/10/2018. With the above method the client will be consider as active from January to October, while actully he was inactive in April and May. 

See the below example:

[Stachu]

hmm, but do you actually need a flag that says "Active"? In the original post you only ask for the count of active customers
with monthly granularity this measure will work as is, also considering the multiple trades complexity

NrOfActiveClients = 
VAR __CurrentMonth = MAX('Calendar'[Date])
VAR __ActiveCustomers = FILTER('Table',__CurrentMonth >= 'Table'[Open trade date] && __CurrentMonth<= 'Table'[Close trade date])
RETURN
COUNTROWS(__ActiveCustomers)