Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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