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 

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.