2 Replies Latest reply on Dec 1, 2015 10:14 PM by Tableau kumar

    Calculate YTD Sales from Actual & Goal

    Christina Taylor

      I have two reports - actual revenue and revenue goal in long format. One lay on top of another. The first column will say report - actual or report - goal. Each is broken down by month, year, and target amount.

       

      I want to calculate (one number) % of achieved revenue against revenue goal - to date. I don't have Nov or Dec actual revenue data yet, as the year has still not ended. It is considered unfair to measure 10 month's performance against 12 month's goal.

       

      To avoid summing up the months where revenue data is not available, I created a seven step calculation:

       

      1. Created a calculated field on monthly recognized revenue:
      If report = recognized revenue, sum(amount)

      [4:01]

      2. Created a calc on monthly goal:
      If report = revenue goal, sum(amount)

      [4:05]

      3. I created a filter to find out until which month do I have actual revenue data (typically a month or two before today's month, but no guarantee)

      [4:05]

      Filter: Sum(recognized revenue)>0

      [4:05]

      4: Running Sum of Recognized Revenue along Month

      [4:06]

      5: Running Sum of Revenue Goal along Month

      [4:06]

      6: Create index ('Position') using Last() to order my months to be included

      [4:07]

      7: Create a 'lookup function' to get the final percentage (green number in heading):

      [4:08]

      If position = 0, Sum(Running Sum of Recognized Revenue)/Sum(Running Sum of Revenue Goal)

      [4:08]

      That sounds like an awful lot of steps to get one number. There has to be a much more efficient way - Like a combination of if statements, fixed, include and exclude...


      See the attached screen shot-

      I present my puzzle. If anyone has a better solution, I'd greatly appreciate hearing about it.