I need help with the calculated column. I am using monthly spread revenue, which is calculated from date and amount, but it is picking up the wrong date from the amount.

Question

I need help with the calculated column. I am using monthly spread revenue, which is calculated from date and amount, but it is picking up the wrong date from the amount.

Potential MRR = Potential TCV / Duration Months

A formula is used to calculate the monthly spread.

Jan 2022 =IF(1-[Duration Months]-MONTH([Forecast Begin Date])>=0,"0",IF(1-MONTH([Forecast Begin Date])<0,"0",IF(1-[Duration Months]-MONTH([Forecast Begin Date])>-1,PMRR,[Potential MRR])))

Likewise, for Feb 2022 =IF(2-[Duration Months]-MONTH([Forecast Begin Date])>=0,"0",IF(2-MONTH([Forecast Begin Date])<0,"0",IF(2-[Duration Months]-MONTH([Forecast Begin Date])>-1,PMRR,[Potential MRR])))

Now, if the duration month is 1 month and the forecast begin date is 01/01/2022, the monthly spread will be displayed in column for Jan 2022. Similarly, if the duration months is 3 months and the forecast begin date is 01/01/2022, the monthly spread will be displayed in columns for Jan 2022, Feb 2022, and Mar 2022.

If I use the date 01/01/2023 or the year 01/01/2021, the monthly spread appears in Columns Jan 2022, Feb 2022, and so on, according to the duration months but not for the Columns Jan 2023, Feb 2023 and so on.

Please help!

Answer 1

Hi @Anonymous ,
Sorry for the delayed response.
According to my research and test, you can add an if statement to the original formula to judge the year.
For example, for column Jan 2023, use the following formula:

=IF(YEAR([Forecast Begin Date])=2023, IF(1-[Duration Months]-MONTH([Forecast Begin Date])>=0,"0",IF(1-MONTH([Forecast Begin Date] )<0,"0",IF(1-[Duration Months]-MONTH([Forecast Begin Date])>-1, "PMRR",[Potential MRR]))) ,"0")  

Note: The year in the condition changes with the year of the column. The 1 in the latter condition maintains the previous change rule.
here is my test:




*
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.