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.

danextian's avatar
danextian
Super User
1 year ago

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.