Forum Discussion
Time intelligence measure
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 |
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) )
1 Reply
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) )