Dax calculate monthy and yearly rent payments

[bois74]

I am trying to create a power bi dax monthy payment calculations on several properties with following data in a table:

1.  lease start dates :  1/5/2022 and 3/6/2020 (and due date for subsequent months)

2.  2 months free :  3/5/2022, 5/5/2020  (payments are 0 and start on these dates)

3.  lease expiration date:  1/5/2028, 3/6/2030

4.  at end of each year rent increase 2%

5.  rent is $950,  $1250

I am hoping to look at each properties cashflow on a monthly and yearly basis.

[jennaw2024]

I am trying to show the increasing costs of our properties but we have multiple escalations based lease dates then, outside of these properties i have some with set % increases year over year and some with % based on the country's inflation index. How do I do this? Even though the leases go back years I was going to start fresh with our fiscal year of 1July2024. Properties Escalation Date CCY Base Rent Monthly (CCY) 1 2025-11-01 AUD 9412.47 1 2025-01-01 NZD 36457.86 1 2026-01-01 NZD 37916.17 2 2024-12-01 USD 29137.53 2 2025-12-01 USD 29825.31 3 2026-07-01 CAD 17788.75 4 2026-05-01 USD 28359.92 4 2027-05-01 USD 29057.29 5 2024-09-01 USD 16846.29 5 2025-09-01 USD 17351.68 6 2024-12-01 USD 124536.65 6 2025-12-01 USD 126645.46 6 2026-12-01 USD 128754.26 6 2027-12-01 USD 130980.22 7 2025-03-01 USD 9374 7 2026-03-01 USD 9569.29 7 2027-03-01 USD 9764.58 7 2028-03-01 USD 9959.88 7 2029-03-01 USD 10155.17 8 2024-11-01 USD 5005.76 8 2025-11-01 USD 5130.9 8 2026-11-01 USD 5259.17 8 2027-11-01 USD 5390.65 9 2024-11-01 USD 36250 9 2025-11-01 USD 36833.33333 9 2026-11-01 USD 38458.33333 9 2027-11-01 USD 43875 9 2028-11-01 USD 44958.33333 10 2024-11-01 ZAR 44049.75833 10 2025-11-01 ZAR 47133.24142 10 2026-11-01 ZAR 50432.56832 10 2027-11-01 ZAR 53962.8481 10 2028-11-01 ZAR 57740.24746 10 2029-11-01 ZAR 61782.06479 11 2026-03-01 GBP 5034.721667 11 2027-03-01 GBP 6250 12 2024-11-01 USD 48925 12 2025-11-01 USD 50392.75 12 2026-11-01 USD 51904.5 12 2027-11-01 USD 53461.66667 12 2028-11-01 USD 64243.08333 12 2029-11-01 USD 66170.41667 12 2030-11-01 USD 68155.5 13 1905-07-16 EUR 5650 14 2031-01-01 USD 194016 15 2025-01-01 USD 6701.5 16 2025-03-01 USD 6020 17 2025-03-01 USD 48679.4 17 2026-03-01 USD 50139.78 17 2027-03-01 USD 51643.97 17 2028-03-01 USD 53193.29 17 2029-03-01 USD 54789.09 17 2030-03-01 USD 56432.76 17 2031-03-01 USD 58125.75 17 2032-03-01 USD 59869.52 17 2033-03-01 USD 61665.6 18 2030-05-01 CAD 105256.5 18 2033-05-01 CAD 109735.5

[lbendlin]

@jennaw2024   please open a new thread.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Gdv]

thanks

[Gdv]

desired out put 

	09-05-2024	31-05-2024	30-06-2024	31-07-2024	31-08-2024	30-09-2024	31-10-2024	30-11-2024	31-12-2024	31-01-2025	28-02-2025	31-03-2025	30-04-2025	31-05-2025
Projected Rental Income as per Base Rent	0.00	1.14	1.80	2.58	3.48	4.50	5.64	6.90	8.28	9.78	13.14	13.14	15.00	16.98
Projected Revenue Share	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Gross Income	0.00	1.15	1.81	2.59	3.49	4.51	5.65	6.91	8.29	9.79	13.15	13.15	15.01	16.99
Balance Cost	0.00	(0.29)							(0.29)
Repairs	0.00	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.18)	(1.18)	(1.18)	(1.18)	(1.18)
Gross Expenses	0.00	(1.41)	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.12)	(1.41)	(1.18)	(1.18)	(1.18)	(1.18)	(1.18)
Net Income	0.00	(0.27)	0.68	1.46	2.36	3.38	4.52	5.78	6.87	8.61	11.97	11.97	13.83	15.81
StartDate	09-05-2024
EndDate	31-10-2056

[Gdv]

Balance Cost	Cost	Date
Balance Cost A	1000	15-02-24
Balance Cost A	1500	15-08-24
Balance Cost A	750	15-08-26
Balance Cost A	1500	15-02-27
Balance Cost A	400	15-02-29
Balance Cost A	300	15-02-30
Balance Cost A	250	15-08-30

[Gdv]

Licensee	GLA sqft	LeaseStartDate	RentCommencementDate	LeaseEndDate	Base Rent	1st Escalation After Years	2nd Escalation After 1st Esclation	3rd Escalation After 2nd	4th Escalation After Years	5th Escalation After Years	Escalation in Base Rent at a	Escalation in Base Rent at b	Escalation in Base Rent at c	Escalation in Base Rent at d	Escalation in Base Rent at e	Base Rent at a	Base Rent at b	Base Rent at c	Base Rent at d	Base Rent at e	Repairs Cost After 1 Year of LeaseStartDate	Escalation %pa	Revenue Share per Annum	Escalation Revenue Share %
						a	b	c	d	e
A	1,000	15-02-24	15-03-24	15-02-33	60	3	2	1	1	1	15.0%	10.0%	3.0%	3.0%	3.0%	91	110	114	117	121	2	1%	11,000	1%
B	1,000	15-02-24	15-04-24	15-02-33	61	2	2	1	1	1	12.0%	10.0%	4.0%	4.0%	4.0%	77	93	96	100	104	2	4%	2,50,000	4%
C	1,000	15-02-24	15-05-24	15-02-33	62	1	2	1	1	1	10.0%	10.0%	5.0%	5.0%	5.0%	68	83	87	91	96	3	5%	25,000	5%
D	7,000	15-02-24	15-04-24	15-02-33	45	2	2	1	1	1	7.0%	4.0%	4.0%	4.0%	4.0%	52	56	58	60	63	4	4%	40,000	4%
						eq
					x	15-02-27	15-02-29	15-02-30	15-02-31	15-02-32						above rent at x dates
						15-02-26	15-02-28	15-02-29	15-02-30	15-02-31

[lbendlin]

Please clarify

1. what calendar is that?  Jan 5 or May 1 ?

2. why 5/5/2020 and not 5/6/2020 ?

4.  End of a full year of lease, end of calendar year, end of full lease while considering the free months?

[Jcharle49]

Property	Rent	Free Rent Months	Lease Start	Lease Expiration	YearlyIncrease
A	950	2	1/5/2022	3/17/2026	0.03
B	1250	2	3/6/2020	10/17/2025	0.03
C	692	4	6/15/2020	3/23/2051	0.03
D	5950	3	9/10/2019	3/23/2024	0.03
E	294	5	11/12/2018	3/25/2026	0.02
F	11675	2	8/20/2018	9/24/2026	0.03
G	1470	5	10/30/2019	11/27/2025	0.0275
H	1500	3	4/1/2020	4/30/2024	0.0275

[lbendlin · Accepted solution]

Your explanations and your sample data do not match.  But here's the adjusted formula

amount = 
VAR a =  SUMMARIZECOLUMNS (
        'Properties'[Property],
        'Properties'[Lease Start],
        'Properties'[Lease End],
        'Properties'[Deferred Months],
        'Properties'[Starting Rent],
        'Properties'[Annual increase],
        Dates[MNo],
        Dates[Year]
    )
VAR b =  ADDCOLUMNS ( a,
        "rent",
            IF (
                FORMAT ( DATE ( [Year], [MNo], 1 ), "yyyymm" )
                    >= FORMAT ( EDATE ( [Lease Start], [Deferred Months] ), "yyyymm" )
                    && FORMAT ( DATE ( [Year], [MNo], 1 ), "yyyymm" )
                        <= FORMAT ( [Lease End], "yyyymm" ),
                [Starting Rent],
                0
            ),
        "factor",
            VAR d =
                INT(DIVIDE(DATEDIFF (
                    EDATE ( [Lease Start], 1),
                    DATE ( [Year], [MNo], 1 ),
                    MONTH
                ),12))
            RETURN
                IF ( d < 1, 1, POWER ( 1 + [Annual increase], d ) )
    )
RETURN
    SUMX ( b, [rent] * [factor] )

see attached.

[Gdv]

Its really amazing, i have little different situation,

1) in some properties rent increases after 2 years or 3 years, what modification i need to do in it,

2) rent is in p sqft p month and area is given so i need to do product of area*rate*factor

3) some cost p sqfr are linked with area, area x cost is required to be deducted

If u can help, would be grateful

[lbendlin]

@Gdv Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bois74]

Yeah, sorry about that.  This is really amazing though.  You got some talent : ) Really teaches me a lot.  Appreciate all your help Ibendlin! 

[bois74]

thank you Ibendin!

there are 2 properties

For 1st property

1.  lease is signed on 1/5/2022 for $950 a month

2.  on 2/5/2022 and 3/5/2022 = $0 monthly rent

3.  on 4/5/2022 through 1/5/2022 = $950 rent is due each month

4.  on 1/5/2023 rent increases $950 * 1.02 = $962 per month but really the $962 cashflow isn't received until next 2/5/2022

5.  on every anniversary it increases until end of lease.  so 2/5/2024, payment increases $962 * 1.02 again until 1/5/2028.

6.  same for property 2 except different dates.  I will try to upload excel file in a minute.