Hi Guys, need help in calculation column in sharepoint

Anonymous
2022-06-20T17:58:03.95+00:00

Hi Guys, need help in calculation column in sharepoint

I have a calculation column where we calculate the revenue in monthly just by dividing the values.

i.e Potential MRR=Potential TCV/Duration Months

let say i have Potential TCV is $27,000 and Duration Months is 2.5 months, so Potential MRR comes out be $10,800

But when it shows in the spread for the months, it shows $10,800 for 3 months instead of 2.5 months which changes the Total if i calculate ($10,800+$10,800+$10,800=$32,400)

Just for reference i have attached the snapshot of the sharepoint sheet.

Please help212920-error-25.png

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,237 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,755 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,576 questions
0 comments
Accepted answer
  1. Yi Lu_MSFT 17,461 Reputation points
    2022-06-23T02:48:10.57+00:00

    Hi @Anonymous
    You could change the months calculated column formula into this, for example "Jan": 

    =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,Potential MRR2,Potential MRR)))

    Here you need to add a calculated column "Potential MRR2":=[Potential TCV]/[Duration Months]*([Duration Months]-ROUNDDOWN([Duration Months],0)). Notice: if value in [Duration Months] is an integer, then the calculated value will be "0", but don't worry, it will not influent the correct value filled in the months columns.

    Then like I mentioned several months before, when you change the other formula in "Feb", "Mar", "Apr"..., just turn the value "1" into "2", "3", "4"...

    For example, the "Feb" calculated formula will be: 

    =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,Potential MRR2,Potential MRR)))

    As a result, your requirement has been achieved:

    214095-image.png

    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.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yi Lu_MSFT 17,461 Reputation points
    2022-06-21T02:49:11.09+00:00

    Hi @Anonymous
    You could add another two calculated columns "Duration Months2": =ROUNDUP([Duration Months],0) and "Potential MRR2"=[Potential TCV]/[Duration Months2] :

    213194-image.png

    213175-image.png

    As a result, we could get another set of data：

    213118-image.png

    You could use the value in Potential MRR2 to fill in the months.

    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.

  2. Yi Lu_MSFT 17,461 Reputation points
    2022-06-21T09:10:56.073+00:00

    Hi @Anonymous
    You could add a new calculated column "Potential MRR2"=[Potential TCV]/[Duration Months]*([Duration Months]-ROUNDDOWN([Duration Months],0))

    213239-image.png

    As a result, you could get the result:

    213324-image.png

    Enter the value in "Potential MRR2" to the last month, enter the value in "Potential MRR" to the other months.

    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.