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.
Anonymous
1 year agoNot applicable
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