cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
icespedes Member
Member

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_ClienteYear
59981 
11993
11994
11995
11996
61997
201998
2821999
7332000
24772001
26892002
56402003
61612004
51862005
56532006
75572007
95582008
96792009
100292010
134362011
233492012
274402013
356982014
360972015
385382016
364372017
376102018
30317

2019

 

Data with the date add formula:

YearClientes ano pasado
19982
199935
2000127
20011984
20021480
20033986
20042855
20052861
20063882
20075419
20086092
20097021
20107720
201110419
201214671
201320492
201428029
201528295
201623678
201727037
201828506
201923142
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Time intelligence measure

@icespedes 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)
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

1 REPLY 1
Super User IV
Super User IV

Re: Time intelligence measure

@icespedes 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)
)





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors