Calculate Rent per month over time

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.

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.

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

HI Tock,

Any update on this? Did these suggestion helps?

Regards,

Xiaoxin Sheng