Calculate Rent per month over time

[Tock]

Hello,

I would like to show the progression of the rent over time. The following table serves as an example:

Customer	Customer Rent	Start Date	End Date
AA	100	01.03.2021
BB	200	01.08.2021	31.10.2024
CC	150	01.06.2022
DD	250	01.02.2023
EE	50	01.05.2024
FF	180	01.02.2025

Now I would like to calculate the total income per month and per year to show the development in 2 charts per month or per year. Months or years where there was no change should still be displayed so that a continuous timeline is shown.

For the rental income per year, the result would be (manually calculated, assuming 2 monthly rents are due in 2025):

Rent yearly |	Amount |	How result is calculated
2021	2000	100 * 10 + 200*5
2022	4650	100*12+200*12+150*7
2023	8150	100*12+200*12+150*12+250*11
2024	8400	100*12+200*10+150*12+250*12+50*8
2025	1280	100*2+150*2+250*2+50*2+180*1

How could I achive this?

[Anonymous]

HI @Tock,

Any update on this? Did these suggestion helps?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Tock ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[danextian]

Hi @Tock 

Create a dates table that is disconnected (without a relationship) from you rent table. The DAX formula below creates a table of dates from the min start date up to the end of the previous month based on today's date.

Dates = 
VAR _End = EOMONTH ( EDATE ( TODAY (), -1 ), 0 )
RETURN
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Rent'[Start Date] ), _End ),
    "Year", YEAR ( [Date] ), "Year Month", FORMAT( [Date], "YYYY-MM" )
)

Create these measures:

Rent = 
VAR _BlankDate =
    EOMONTH ( EDATE ( TODAY (), -1 ), 0 )
VAR _ValidDates =
    SUMMARIZE (
        FILTER (
            Dates,
            Dates[Date] >= MINX ( Rent, Rent[Start Date] )
                && Dates[Date] <= COALESCE ( MAXX ( Rent, Rent[End Date] ), _BlankDate )
        ),
        Dates[Year Month],
        Dates[Year]
    )
RETURN
    SUMX ( _ValidDates, SUM ( Rent[Customer Rent] ) )

Total Rent = 
SUMX ( VALUES ( Rent[Customer] ), [Rent] )

Please see the attached sample pbix.

[johnt75 · Accepted solution]

You could create a measure like

Rent = 
VAR MinDate = MIN( 'Date'[Date] )
VAR MaxDate = MIN( MAX( 'Date'[Date] ), EOMONTH( TODAY(), -1 ) )
VAR SummaryTable = GENERATE(
	SELECTCOLUMNS(
		Rent,
		Rent[Customer],
		Rent[Customer Rent],
		"@StartDate", Rent[Start Date],
		"@EndDate", COALESCE( Rent[End Date], MaxDate )
	),
	FILTER(
		DATESBETWEEN( 'Date'[Date], [@StartDate], [@EndDate] ),
		DAY( 'Date'[Date] ) = DAY( [@StartDate] )
	)
)
RETURN SUMX( 
	FILTER(
		SummaryTable, 
		'Date'[Date] >= MinDate && 'Date'[Date] <= MaxDate
	),
	Rent[Customer Rent] 
)

For each customer it generates a list of dates when the rent would be due, and then sums all those rents which are due during the selected period.

[FarhanJeelani]

To calculate the rent progression over time in Power BI, follow these steps:

Step 1: Create a Date Table
Since you need a continuous timeline, create a Date table:

DateTable = ADDCOLUMNS(
CALENDAR(DATE(2021,1,1), DATE(2025,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "YYYY-MM")
)

Step 2: Expand Rent Periods for Each Month
Since each rent period has a start and (possibly) an end date, we need to generate a table that expands rent data for each month.

Use Power Query to create a new table:

Expand rental periods: Create a list of months between [Start Date] and [End Date] (or today if no end date).
Expand rows: Each row represents rent applied to that specific month.

Or, in DAX, create a new table:

RentExpanded =
VAR MaxDate = TODAY()
RETURN
ADDCOLUMNS(
FILTER(
CROSSJOIN('DateTable', RentTable),
RentTable[Start Date] <= DateTable[Date] &&
(ISBLANK(RentTable[End Date]) || RentTable[End Date] >= DateTable[Date])
),
"Monthly Rent", RentTable[Customer Rent]
)

Step 3: Calculate Monthly and Yearly Rent
Create measures:

Monthly Rent Calculation

Total Monthly Rent =
SUMX(
RentExpanded,
RentExpanded[Monthly Rent]
)

Yearly Rent Calculation

Total Yearly Rent =
CALCULATE(
[Total Monthly Rent],
ALLEXCEPT('DateTable', 'DateTable'[Year])
)

Step 4: Create Visuals
Monthly Rent Chart:

X-Axis: DateTable[Month]
Y-Axis: Total Monthly Rent
Yearly Rent Chart:

X-Axis: DateTable[Year]
Y-Axis: Total Yearly Rent

Please mark this post as solution if it helps you. Appreciate Kudos.