Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Monthly Active Clients

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.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

6 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.