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 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. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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