I have 2 table, first one is the payment of each invoice, and another one is member database.
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.
Go to Solution.
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(
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]]]),
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),
That should be it. Hope it helps!
View solution in original post
Hey @Mark022 ,
I have tried to implement your ask. See if this is what you are after.
Attaching the pbix file here.
Click here to learn more about the September 2022 updates!
Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!