Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola buenas tardes
estoy trabajando desde hace poco con PBi y me solicitan un informe:
tengo una tabla cliente = DNI,.....
tengo una tabla visita = dni, fecha visita
se necesita una tabla (entiendo que debido a ello debe ser calculada) donde dado un año (este es un filtro) salgan aquellos clientes que solo vinieron una vez y que en años anteriores vineiron 0 veces, es decir NUEVOS y que al año siguiente cuantas veces vinieron
por ejemplo si se selecciona 2018, salgan los nuevos del 2018 (no vinieron en años anteriores) y cuantas veces vinieron el siguiente año
necesitaria por favor consejo de como abordar este tema.
Solved! Go to Solution.
To see new customers in a new customer table in a year with the dates they returned to the following year you can:
1) Create a measure to identify the dates of visit for the following year:
Visitas Año siguiente =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Tabla Visitas'[Fecha Visita] ),
FILTER (
ALL ( 'Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] ) + 1
)
)
)
2) create a measure to identify which customers returned the following year:
ID del año selecc con visitas en el año siguiente =
VAR IDs =
CALCULATETABLE (
VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
< SELECTEDVALUE ( 'Tabla Calendario'[Año] )
)
)
VAR IDsASelecc =
CALCULATETABLE(VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] )
)
)
VAR IDNY = CALCULATETABLE(VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] ) +1
)
)
RETURN
COUNTROWS(
INTERSECT(IDNY,
EXCEPT(IDsASelecc, IDs)
)
)
Create the table with the CustomerID and Visit Date fields in the Visits table, add the [next year visits] measure that we just created.
Finally, select the visual, go to the filter pane, and in the ClientID field, select "filter type" - TopN, add the measure [ select year ID with visitsin the following year] that we just created and set the value equal to 1.
It'll look like this to you:
I attach the new PBIX file to you
Proud to be a Super User!
Paul on Linkedin.
Hola buenas tardes
imaginemos la primera tabla de clientes:
dni | nombre |
1 | juan |
2 | pedro |
3 | susana |
4 | irene |
5 | rafa |
y la segunda tabla de visitas
dni | visita |
1 | 05/07/18 |
2 | 06/08/18 |
3 | 01/02/19 |
4 | 02/02/19 |
1 | 03/02/19 |
3 | 04/01/20 |
5 | 03/01/20 |
3 | 07/02/20 |
5 | 03/01/20 |
3 | 07/02/20 |
si seleciono el 2019 veo que ha venido 3,4 y 1
1 descartado porque es repetidor y solo me interesan los nuevos,entonces solo me interesa 3 y 4 porque son nuevos entonces quiero saber cuantas veces han venido incluido cero.
La intencion es montar un dashboard para medir la repeticion del cliente. lo dificil es captar un cliente nuevo, entonces quiero saber si las acciones que hago, permiten que se convierta en repetidor.
y con esa nueva tabla ya montaré un indicador de porcentaje de repetidores, es decir, cuantos nuevos del año seleccionado repetirieron el siguiente año?
muchas gracias!
Thank you for providing the data samples. Let's see if here's what you're looking for:
First, the data model (I created a calendar table because it is recommended)
The measure for calculating new visits is:
Nuevas Visitas =
VAR IDs = // to calculate de client IDs which have visited before the selected year
CALCULATETABLE (
VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ( 'Tabla Visitas' ),
YEAR ( 'Tabla Visitas'[Fecha Visita] )
< SELECTEDVALUE ( 'Tabla Calendario'[Año] )
)
)
VAR IDsASelecc = // to calculate de client IDs which have visited in the selected year
VALUES ( 'Tabla Visitas'[ID Client] )
RETURN
SUMX (
SUMMARIZE (
EXCEPT ( IDsASelecc, IDs ), //creates a table which only includes IDs which are present in IDsASelecc but not in IDs
"Number", DISTINCTCOUNT ( 'Tabla Visitas'[Fecha Visita] )
),
[Number]
)
I attach the PBIX file to the message so you can explore it.
Proud to be a Super User!
Paul on Linkedin.
Hello
thank you very much for the example, but there is an important detail, I do not want to count the visits of that year for example 2019 (it is ok to have it) but the following
that is, if I select 2019, which customers are new?, but which customers have repeated in 2020?
this way I know who haven't repeated and I'm going to take action. the number column of viistas is the same year of the filter and I need the next
Thanks again
To see new customers in a new customer table in a year with the dates they returned to the following year you can:
1) Create a measure to identify the dates of visit for the following year:
Visitas Año siguiente =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Tabla Visitas'[Fecha Visita] ),
FILTER (
ALL ( 'Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] ) + 1
)
)
)
2) create a measure to identify which customers returned the following year:
ID del año selecc con visitas en el año siguiente =
VAR IDs =
CALCULATETABLE (
VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
< SELECTEDVALUE ( 'Tabla Calendario'[Año] )
)
)
VAR IDsASelecc =
CALCULATETABLE(VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] )
)
)
VAR IDNY = CALCULATETABLE(VALUES ( 'Tabla Visitas'[ID Client] ),
FILTER (
ALL ('Tabla Calendario'[Año] ),
'Tabla Calendario'[Año]
= SELECTEDVALUE ( 'Tabla Calendario'[Año] ) +1
)
)
RETURN
COUNTROWS(
INTERSECT(IDNY,
EXCEPT(IDsASelecc, IDs)
)
)
Create the table with the CustomerID and Visit Date fields in the Visits table, add the [next year visits] measure that we just created.
Finally, select the visual, go to the filter pane, and in the ClientID field, select "filter type" - TopN, add the measure [ select year ID with visitsin the following year] that we just created and set the value equal to 1.
It'll look like this to you:
I attach the new PBIX file to you
Proud to be a Super User!
Paul on Linkedin.
Thanks a lot!
Puedes por favor facilitarnos una muestra de las tablas (en formato datos; no como imagen) para que podamos preparar la solulción?
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |