Forum Discussion
Tock
1 year agoNew Member
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...
- 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.
johnt75
1 year agoSuper User
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.