1 2 Previous Next 15 Replies Latest reply on Aug 15, 2018 5:31 AM by Ankit Bansal

    Advanced calculation result vs budget

    daniel.andell

      I have sales and budget data in my data and I would like to show the managers how much they need to increase/decrease the sales for the rest of the year to reach the budget.

       

      For example if we have sales Jan-Jul on 500 and a budget for the same period on 1000. Then 500 needs to be additional sales for Aug-Dec to reach the budget.

       

      In my data it means

      I would like to make a calculation that looks at all full months of sales vs budget. I have Jan-Jul as full months.

      We are -37 411 vs budget from Jan-Jul. The total budget for the upcoming months is 410 701.

      This means that the budget has to be exeeded by 37 411 for the rest of the months to reach end of the year budget.

      In percent this means that the budget has to be exeeded by 9,1% (new target (37411+410701) / old target (410701) = percent needed (109,1%)

       

       

      My question is how I can do this calculation.

      1. I first need to look at the sales-budget from -1 month from today to only look at full months

      2. I then need to look at the budget from current month to end of the year.

      3. last I need to do the following calculation: (calculation 1 + calculation 2) / Calculation 2

       

      This formula is used in Another calculation, but I dont get it all to work in this case.

      FLOAT(if [Sales_Month] >= MONTH(TODAY()) then [Sales] else NULL

       

        1 2 Previous Next