Forum Discussion

Chief's avatar
Chief
Helper II
1 year ago
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...
Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    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

     

     







Chief's avatar
Chief
Helper II
1 year ago

Fowmy - This is helpful, however, now it is adding a day to any contract that is greater than a same day rent. To specify, if an asset goes out on 7/20/25 and is returned 7/20/25, customer is charged one day. If asset goes out 7/20/25 and returned 7/21/25 customer is charged one day. If asset goes out 7/20/25 and returned 7/22/25 customer is charged 2 days, and so on. The only difference in calculation is an asset that leaves and comes back on the same day.

Fowmy's avatar
Fowmy
Super User
1 year ago

Chief 

Okay, got you, this should work for you:

DaysOnRentEachMonth = 
VAR __MonthDays = VALUES(RentalContractCalendar[Date])
VAR __T = 
  
    SUMX(
        RMDETL,
        VAR __Duration = CALENDAR(RMDETL[DateOut],RMDETL[DateIn])
        VAR __Days = COUNTROWS( INTERSECT( __Duration,__MonthDays) )
        RETURN
           IF( __Days > 1, __Days - 1 , __Days  )
    )
RETURN
    __T