Forum Discussion
Solved
Days on Rent calcuation
Hello, I have data that is compromised of a rental company. The data shows ID#, Contract#, Rental Begin Date (DateOut) and Rental Return Date (DateIn). I have followed an example to count the number...
Chief
Thanks for your kind words!
I modifed the formula, also remove the In date of the last line to test it.DaysOnRentEachMonth = VAR __MonthDays = VALUES(RentalContractCalendar[Date]) VAR __T = SUMX( RMDETL, VAR __Out = RMDETL[DateOut] VAR __In = COALESCE(RMDETL[DateIn],TODAY()) VAR __Duration = CALENDAR(__Out,__In) VAR __Days = COUNTROWS( INTERSECT( __Duration,__MonthDays) ) RETURN IF( __Days > 1, __Days - 1 , __Days ) ) RETURN __T
Fowmy - This works wonderfully! However, there is one last piece I'm struggling with. If an asset is still on rent (i.e. 'Blank' DateIn), I would like to account for that by changing any 'Blank' in the DateIn field to today's date 'Today()'. I've tried adding a variable and adding it into the first return statement to no avail.
Chief
Thanks for your kind words!
I modifed the formula, also remove the In date of the last line to test it.
DaysOnRentEachMonth =
VAR __MonthDays = VALUES(RentalContractCalendar[Date])
VAR __T =
SUMX(
RMDETL,
VAR __Out = RMDETL[DateOut]
VAR __In = COALESCE(RMDETL[DateIn],TODAY())
VAR __Duration = CALENDAR(__Out,__In)
VAR __Days = COUNTROWS( INTERSECT( __Duration,__MonthDays) )
RETURN
IF( __Days > 1, __Days - 1 , __Days )
)
RETURN
__T