Days on Rent calcuation

[Chief]

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:

[Ashish_Mathur]

Hi,

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

[Chief]

@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]

@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

[Fowmy]

@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]

@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 · Accepted solution]

@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]

Huge help @Fowmy. Much appreciated! Coalesce acts as a nested if/then funtion, that 's the fuction I needed. Many thanks.

[v-shex-msft]

HI @Chief,

This difficult should based on you 'blank' scenarios.
If the ‘blank’ mean this record really include in your table and not recorded with fact values, It can be simply check and replace with other values.
If the 'blank' means these records not really include in your table, you may need to use other table field with full value of category to expand the axis and lookup the raw table records based on current category value and manually return result value on the not existed record.
Regards,

Xiaoxin Sheng