Count the number of occurrences per year

[Mark022]

I have 2 table, first one is the payment of each invoice, and another one is member database.

Payment Table 

[user_code]	[payment_date]	[payment_type]
user_A	1/1/2022	A
user_B	1/6/2021	A
user_A	1/6/2021	B
user_C	1/3/2022	A
user_C	1/3/2021	A

Member Table

[user_code]	[active]
user_A	active
user_B	active
user_C	inactive

Need help to find out the following question. 

1. add 2 column to Member Tabe to find out the number of times each user enrolled in the course TYPE A/ TYPE B this year

2. Calculate how many users have made payment_type A per year. 
3. Calculate how many users have made no payment this year but active. 

[PC2790]

Hey @Mark022 ,

I have tried to implement your ask. See if this is what you are after.

Attaching the pbix file here.

[TomasAndersson · Accepted solution]

Hi!

I'm assuming you have a relationship between the two tables, with [user_code] as key.

Let's take the questions one by one:

1. Create your columns. Do as below for both (switching out for "B" in the second case). You're counting how many cases there are fore each user_code that has a certain payment_type.

enrolledA = CALCULATE(
    counta(PaymentTable[[user_code]]]),
    PaymentTable[[user_code]]] = earlier(MemberTable[[user_code]]]),
    PaymentTable[[payment_type]]] = "A")

2. Using your new column, create a measure that calculated the number of payments:

UsersPaidA = CALCULATE(COUNT(MemberTable[[user_code]]]),
    MemberTable[enrolledA]>0)

Using this measure along with a date in any visual will get you the number of payments per year.

3. I did this with two measured. First, the number of payments this year:

PaymentsThisYear = calculate(Count(PaymentTable[[payment_date]]]),
    YEAR(PaymentTable[[payment_date]]]) = year(TODAY()))

Then, I used this measure to see how many users that have 0 payments but still an active status.

NoPaymentsButActive = CALCULATE(COUNT(MemberTable[[user_code]]]),
    FILTER(MemberTable,[PaymentsThisYear] = 0),
    MemberTable[[active]]]="active")

That should be it. Hope it helps!