cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Get monthly rent for each tenant based on date selection

Hi, I've got another problem here that I know probably has some simple solution but I just can't figure out the logic on this. I'm trying to get the monthly rent for each tenant based on selected month in a date slicer. So when I click on a month, it will show the rent charged for that month because some tenants have rent increases during the year. The date slicer is month/year from a separate date table. I also have a separate measure called Total Monthly Rent.

Here is my DAX code but I just keep getting the same rent amount for every tenant. The total is also the same value.

Total Monthly Rent = SUM( 'Rent Roll'[Monthly Rent]

Tenant Rent =
MAXX(
DISTINCT( 'Rent Roll'[Tenant] ),
MAXX(
FILTER(
DISTINCT( 'Rent Roll'[Date] ),
'Rent Roll'[Date] = CALCULATE( MAX( 'Date'[Date] ) )
),
[Total Monthly Rent]
)
)

Date Tenant Monthly Rent
5/31/2019 Tenant One \$10,000
6/30/2019 Tenant One \$10,000
7/31/2019 Tenant One \$10,000
8/31/2019 Tenant One \$10,000
9/30/2019 Tenant One \$10,000
10/31/2019 Tenant One \$11,000
11/30/2019 Tenant One \$11,000
12/31/2019 Tenant One \$11,000
1/31/2020 Tenant One \$11,000
2/29/2020 Tenant One \$11,000
3/31/2019 Tenant Two \$5,000
4/30/2019 Tenant Two \$5,000
5/31/2019 Tenant Two \$5,000
6/30/2019 Tenant Two \$5,000
7/31/2019 Tenant Two \$5,000
8/31/2019 Tenant Two \$5,000
9/30/2019 Tenant Two \$5,000
10/31/2019 Tenant Two \$5,000
11/30/2019 Tenant Two \$5,000
12/31/2019 Tenant Two \$5,000
1/31/2020 Tenant Two \$5,000
2/29/2020 Tenant Two \$5,000
5/31/2019 Tenant Three \$7,000
6/30/2019 Tenant Three \$7,500
7/31/2019 Tenant Three \$7,500
8/31/2019 Tenant Three \$7,500
9/30/2019 Tenant Three \$7,500
10/31/2019 Tenant Three \$7,500
11/30/2019 Tenant Three \$7,500
12/31/2019 Tenant Three \$7,500
1/31/2020 Tenant Three \$7,500

Thanks again!

1 ACCEPTED SOLUTION
Community Support

Hi, @kwats

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EYtpBNbDENdFk4XVY...

And you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

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

2 REPLIES 2
Community Support

Hi, @kwats

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EYtpBNbDENdFk4XVY...

And you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

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

Super User

@kwats , if your date is joined with the date table and you are filtering month from there and you want to see monthly rent. Simple sum should do?

sum(Table[Monthly Rent])