Hi Juned,

Have you already started working on a formula to streamline this allocation? You might find the PRORATA() function helpful for distributing rent amounts based on a given period. 

Hello ​@Tanya Krytsyna ,

Thank you for the suggestion. Prorata function works. I am currently facing an issue where it will start prorating every 1st month of the first year lease contract. I want the formula to only prorate the 1st month of the 1st lease contract while it takes the full value for all the subsequent 1st of month of the other lease contract.
EG: Store 1 Yr 1 annual rent is 100k
        Store 1 Yr 2 annual rent is 120k
        Store 1 Yr 3 annual rent is 140k

in the lease contract- due to prorata formula- the total rent for the year will be around 99k due to the 1st month being prorated. The 2nd and 3rd year will also prorate the 1st month (118k,139K). 

Ideal scenario - I want only the 1st month of the 1st year lease contract to prorate EG- annual rent Y1 is 100k I get a total of 99k due to 1st month being prorated (99k). Whereas, for Y2 and Y3, I want the 1st month not to be prorated but the value to be taken (120k,140k)

Current formula 
 

Hi ​@Juned.husain 

To ensure only the first month of the first lease contract is prorated, while subsequent years take the full value, the following adjustments are needed:

• IF Condition: Check if the lease start year is the earliest for that store. If true, apply prorata only to the first month; otherwise, take the full monthly value.

  IF(
      YEAR('Rent Schedule'.'Start date') = MIN(YEAR('Rent Schedule'.'Start date') BY 'Rent Schedule'.Store_Name), 
      PRORATA(Month, 'Rent Schedule'.'Start date', 'Rent Schedule'.'End date') * 'Rent Schedule'.'Monthly Value',
      'Rent Schedule'.'Monthly Value'
  )

   

• Grouping by Store: Ensure the calculation applies prorata only for the first lease period per store, preventing unintended proration in later years.
• Filters & Aggregations: Existing filters and summation logic remain unchanged.

This ensures correct proration only for the first lease contract while keeping full values for subsequent years. Let me know if you need further tweaks!