Active Clients

[ContabilidadBI]

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.

[Anonymous · Accepted solution]

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]

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]

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

How will do it ?

Thanks.

[ContabilidadBI]

Bro, don't you have your own thread where me and others answered you? don't want to be rude but lets keep an order please.

Thanks.

[AmalrajRRD1]

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

[ContabilidadBI]

@AmalrajRRD1I answered in your own thread.

Any idea guys how can I make this active clients calculation?

Thanks