Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
jsama
Frequent Visitor

Rastreo de un grupo

Hola, buen día, necesito ayuda con lo siguiente:

Tengo una tabla de clientes, donde tengo una columna de fecha de inscripción (alta) y una columna de fecha en que cancelaron la inscripción (baja). Con ayuda de un filtro de fecha puedo filtrar las personas que se inscribieron por mes y por año, así como los que cancelaron su inscripción en dicho mes y dicho año. Sin embargo, me interesa ver en qué mes cancela su inscripción un grupo en específico, por ejemplo, quiero filtrar el grupo que se inscribió en enero de 2022, y ver cuántos cancelaron su inscripción en febrero, cuántos cancelaron su inscripción en marzo, en abril, etc. y cuántos siguen activos al día de hoy.

1 ACCEPTED SOLUTION

Hi @jsama ,

 

I suggest that you can create more measures to show in which months they cancel their registration.

Please follow these steps.

January = CALCULATE ( COUNT ( 'Table'[End month] ), 'Table'[End month] = 1 )
Febuary = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 2 )
March = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 3 )
April = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 4 )
May = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 5 )
June = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=6) 
July = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=7) 
August = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=8) 
September = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=9) 
October = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=10) 
November = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=11) 
December = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=12) 
number of active states = CALCULATE(COUNT('Table'[End month]),'Table'[Saart month]=SELECTEDVALUE('Table'[Saart month]),'Table'[End month]=0) 

And put them into a chart, you will see clearly in which months they cancel their registration.

vxiaosunmsft_0-1666598246878.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
jsama
Frequent Visitor

La información relevante es así, está el número de socio (número de cliente), la fecha de alta (fecha de inscripción), la fecha de baja (fecha de cancelación de la inscripción), el estado (alta o baja) y los días de actividad (que es fecha de baja menos fecha de alta si está de baja, o la resta entre hoy y la fecha de alta si está de alta). Si tomamos como ejemplo los que se inscribieron en enero, que como se puede ver en la tabla, son 7, lo que necesito es poder ver de alguna manera al seleccionar a este grupo cuántos se dieron de baja en cada mes, haciéndolo manual se puede ver que en febrero se dieorn de baja 3, en marzo 2 y quedan 2 activos. Esto es lo que necesito, por si alguien conoce algún método que me pueda ayudar.

jsama_1-1665517283917.png

 

 

Hi @jsama ,

 

According to your description, here is my solution.

Create two measures to display the month of all registrations and cancellations.

 

Saart month =
MONTH ( 'Table'[date of registration] )
End month =
IF (
    'Table'[date of cancellation] = BLANK (),
    0,
    MONTH ( 'Table'[date of cancellation] )
)

 

vxiaosunmsft_1-1665567750419.png

Create a measure to show the number of cancellations after one month.

 

number of cancellations after one month =
VAR a =
    CALCULATE (
        COUNT ( 'Table'[End month] ),
        'Table'[End month]
            = SELECTEDVALUE ( 'Table'[Saart month] ) + 1
    )
RETURN
    a

 

 Create a measure to show the number of cancellations after two month.

 

number of cancellations after two month =
VAR b =
    CALCULATE (
        COUNT ( 'Table'[date of cancellation] ),
        'Table'[End month]
            = SELECTEDVALUE ( 'Table'[Saart month] ) + 2
    )
RETURN
    b

 

Create a measure to show the number of active states.

 

number of active states = CALCULATE(COUNT('Table'[End month]),'Table'[Saart month]=SELECTEDVALUE('Table'[Saart month]),'Table'[End month]=0) 

 

 Put "Saart Month" into slicer, and choose the month you need, you will get the final output.

vxiaosunmsft_0-1665567430951.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much, this is almost what i need. Let me tell you that the data that use as example is incomplete. In the original data i have clients that cancell after 3, 4, 5 or even more months, what can i do in this case? Im looking for something like the image, i want to select the group that registered in one month and look in wich months they cancell their registration, is that posible? 

jsama_0-1665613944339.png

 

Hi @jsama ,

 

I suggest that you can create more measures to show in which months they cancel their registration.

Please follow these steps.

January = CALCULATE ( COUNT ( 'Table'[End month] ), 'Table'[End month] = 1 )
Febuary = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 2 )
March = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 3 )
April = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 4 )
May = CALCULATE ( COUNT ( 'Table'[date of cancellation] ), 'Table'[End month] = 5 )
June = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=6) 
July = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=7) 
August = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=8) 
September = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=9) 
October = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=10) 
November = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=11) 
December = CALCULATE(COUNT('Table'[date of cancellation]),'Table'[End month]=12) 
number of active states = CALCULATE(COUNT('Table'[End month]),'Table'[Saart month]=SELECTEDVALUE('Table'[Saart month]),'Table'[End month]=0) 

And put them into a chart, you will see clearly in which months they cancel their registration.

vxiaosunmsft_0-1666598246878.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors