Time intelligence measure

[Anonymous]

Hi,

Im trying to get the count of clients per day in the last year. I got the real number using the distinct count of "Codigo_Cliente" by "Fecha_Primer_Producto".

To get the last year number, Im using the following formula:

Last year clients =

CALCULATE(

    DISTINCTCOUNT('Clientes Hoy'[Codigo_Cliente]),

FILTER('Clientes Hoy',DATEADD('Clientes Hoy'[Fecha_Primer_Producto],-1,YEAR))

)

However, I dont get the right number. Can you please help me?

Data here:

Normal data

Count of Codigo_Cliente	Year
59981
1	1993
1	1994
1	1995
1	1996
6	1997
20	1998
282	1999
733	2000
2477	2001
2689	2002
5640	2003
6161	2004
5186	2005
5653	2006
7557	2007
9558	2008
9679	2009
10029	2010
13436	2011
23349	2012
27440	2013
35698	2014
36097	2015
38538	2016
36437	2017
37610	2018
30317	2019

Data with the date add formula:

Year	Clientes ano pasado
1998	2
1999	35
2000	127
2001	1984
2002	1480
2003	3986
2004	2855
2005	2861
2006	3882
2007	5419
2008	6092
2009	7021
2010	7720
2011	10419
2012	14671
2013	20492
2014	28029
2015	28295
2016	23678
2017	27037
2018	28506
2019	23142

[parry2k · Accepted solution]

@Anonymous you need to add calendar table in your model, there are many posts on how to do it. one you have this table, set relationship with your transaction table and then update your measure as below

Last year clients = 
CALCULATE(
    DISTINCTCOUNT('Clientes Hoy'[Codigo_Cliente]),
DATEADD(Calendar[Date],-1,YEAR)
)