cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FC_de22
Frequent Visitor

Checking for rent monthly payments in two tables

Hello,

 

I have two tables, one with the details of the tenants and one with the payments received (bank statement where tenants pay). Payments from the tenants can be rent or other payments.

 

How can I create a colum to see if someone has payed the rent and for which month?

 

The tables have several columns but these are the important ones:

 

Table 1

 

Tenant ID

Name

Rent Amount

 

6546

Doe

350

 

2347

Poll

500

 

2345

Potter

400

 

 

Table 2

 

Date

Payer

Payment Reason

Amount

 

 

Tenant ID

10/8/2022

Potter

Rent Potter

400

 

 

2345

10/1/2022

Doe

Rent + Deposit

500

 

 

6546

10/1/2022

Citybank

Rent Poll

500

 

 

2347

10/1/2022

Citybank

Deposit Poll

100

 

 

2347

 

 

Column "Tentant ID", I created (in Table 2) to be able to relate the two tables.

 

This is the column I need:

 

Monthly payment received: has a tenant pay his rent? we need to check in either column "Payer" or "Payment Reason" if "Tenant" has made a payment. Unfortunately both columns have to be checked as tenants sometimes pays directly with the bank account and sometimes go to the bank an pay, so their name can appear in either column. We also need to checked his actual due monthly payment (in Table 1) and when the payment was made (month).

 

Tenant ID

Name

Rent Amount

Monthly payment

Overall payment received

Other payments

6546

Doe

350

October

500

Deposit

2347

Poll

500

October

600

Deposit

2345

Potter

400

October

400

None

 

I made already column Overall Payment received but I do not know how to combine the date. From the overall payment column one can deduce what was the payment for.

 

I would appreciate your help.

 

 

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @FC_de22 ,

 

Hope it helps you. I made a .pbix file for you. It is finished by creating measures.

vstephenmsft_0-1667964246350.png

 

 

Best Regards,

Stephen Tao

 

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

Hello Stephen,

 

many thanks for your reply and sensind the sample. I have a question regarding the measure Overall Payment Received, this is a sum, but is it possible to get the actual payment received for a given Month? 

 

That is, find out how much a given tenant has paid in a given month, I am assuming the expression has to combine a filtering for the column Tenant ID in the Amount column and somehow involve the date.

 

I tried getting rid of the SUM in the CALCULATE statement but it does not work. 

 

Thanks!

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors