Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Helper III
9 years ago
Solved

Active Clients

Hello guys,

 

I want to create a simple measure that calculates the number of active clients, considerating active clients, clients who bought something in the last 3 months for example. If a client bought something 100 days ago and didn't buy anything since then, he is not an active client. So it would be something like this:

 

ActiveClients = CALCULATE ( DISTINTCOUNT( Sales [ClientKey], ---between today and 3 months ago---

 

Any idea how can I achieve that? I have a separate Calendar Table and the model looks like this:

 

 

 

Calendario is the date table, Facturs is the sales table. CodCliente is the clientkey I want to DISTINTCOUNT

 

Thanks!!! I am learning so much in this forum.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI ContabilidadBI.

     

    If these tables has relationship, you can try to use below formula to calculate the distinct count.

     

    ActiveClients = CALCULATE ( DISTINTCOUNT( Sales [ClientKey]), FILTER(ALL(Calendar),[Date]>= Date(Year(Today()),Month(Today())-3),day(today())))

    In addition, if above formula not works, I'd like to suggest you modify the 'cross filter direction' option to 'both' and try again.

     

    Regards,

    Xiaoxin Sheng

6 Replies

  • AmalrajRRD1's avatar
    AmalrajRRD1
    Helper II
    9 years ago

    I found the solution. Now my question is how will show only last month subtraction

    for example i have five years data (2012-2017). I want to show the subtraction only between 2017 and 2016.

     

    is possiable can do it .

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI ContabilidadBI.

     

    If these tables has relationship, you can try to use below formula to calculate the distinct count.

     

    ActiveClients = CALCULATE ( DISTINTCOUNT( Sales [ClientKey]), FILTER(ALL(Calendar),[Date]>= Date(Year(Today()),Month(Today())-3),day(today())))

    In addition, if above formula not works, I'd like to suggest you modify the 'cross filter direction' option to 'both' and try again.

     

    Regards,

    Xiaoxin Sheng

    • ContabilidadBI's avatar
      ContabilidadBI
      Helper III
      9 years ago

      Hi Anonymous,

       

      Thank you for your help. I am trying to use the formula but I have a problem, this is the formula with the names of my tables/columns:

       

      ClientesActivos = 
CALCULATE(
	 DISTINCTCOUNT(Facturas[CodCliente]);
	 FILTER(
		 ALL(Calendario);
		 Calendario[Fecha]>= CALENDAR(YEAR(TODAY());MONTH(TODAY())-3);DAY(TODAY())))

      I get an error that says that too many arguments are passed to the FILTER function, which only admits two. Am I doing anything wrong? The sales table (Facturas), and the calendar table (Calendario) are related, as you can see in the pic.

       

      Thanks again.

       

       

      • AmalrajRRD1's avatar
        AmalrajRRD1
        Helper II
        9 years ago

        Now I want to find latest month, latest quarter and Latest year differences value.

         

        How will do it ?

         

        Thanks.