Forum Discussion

Tock's avatar
Tock
New Member
1 year ago
Solved

Calculate Rent per month over time

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 0...
Need Help
Show and Tell
  • johnt75's avatar
    johnt75
    1 year ago

    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's avatar
FarhanJeelani
Super User
1 year ago

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.