Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kwats
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
v-robertq-msft
Community Support
Community Support

Hi, @kwats 

According to your requirement, I created a test pbix file based on your sample data, you can download from here:

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

 

And you can get what you want, like this:

v-robertq-msft_0-1603242966260.png

 

 

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, @kwats 

According to your requirement, I created a test pbix file based on your sample data, you can download from here:

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

 

And you can get what you want, like this:

v-robertq-msft_0-1603242966260.png

 

 

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.

amitchandak
Super User
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])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.