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.

296 Reputation points
2022-11-19T06:45:03.667+00:00

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.

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
1,797 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
7,076 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
1,975 questions

1. 10,846 Reputation points Microsoft Vendor
2022-11-24T07:32:06.79+00:00

Hi @Sumitesh Kumar ,
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:

*