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 of days an asset is on rent. This example seems to work or me, however, if something is  rented and returned on the same day, I need my formula to calculate 1 day of rent; it is currently counting it as zero (see example on contract 014760).

 

The other issue I have is that the entry that goes from December to January, contract 015416, doesn't show any days rented for January. I have confirmed my calendar date goes way beyond that date. Any help would be appreciated:

 

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

     

     







8 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    Chief 

    My understanding is that both dates should be inclusive. You're getting zero for one instance because you're excluding a day from the rental period. Additionally, you need to iterate over each contract to get the correct number of rental days.

    Try the following DAX measure:



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

     

     

    • Chief's avatar
      Chief
      Helper II
      1 year ago

      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. 

      • Fowmy's avatar
        Fowmy
        Super User
        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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share some data to work with and show the expected result very clearly.