cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mark022
Regular Visitor

Count the number of occurrences per year

 

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_A1/1/2022A
user_B1/6/2021A
user_A1/6/2021B
user_C1/3/2022A
user_C1/3/2021A

 

Member Table

[user_code][active]
user_Aactive
user_Bactive
user_Cinactive

 

 

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. 

1 ACCEPTED SOLUTION
TomasAndersson
Frequent Visitor

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!

 

 

View solution in original post

2 REPLIES 2
PC2790
Super User
Super User

Hey @Mark022 ,

 

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

Attaching the pbix file here.

 

TomasAndersson
Frequent Visitor

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!

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors