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

     

     







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

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

  • 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