Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi, @kwats
According to your requirement, I created a test pbix file based on your sample data, you can download from here:
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.
Hi, @kwats
According to your requirement, I created a test pbix file based on your sample data, you can download from here:
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.
@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])
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |