9 Replies Latest reply on Mar 6, 2017 9:59 AM by Deepak Rai

    Allocating amounts across a date range in Salesforce

    Dani Cheng

      Hi,

       

      I currently have a revenue reporting issue with Tableau that I need some help answering. In Salesforce, when one of our customers books service, that service can sometimes span multiple months but only one dollar amount is given. This amount is split up by the number of months in Salesforce. For example, if I get a $60,000 for service running from January to June, Salesforce will allocate $10,000 revenue to each of these months.

       

      When I connect to Tableau, I get the sum amount. I can create a calculated field to capture the first month, but then I can't pick up any subsequent months.

       

      IF INT(DATEPART("month", [End Date])) - INT(DATEPART("month", [Start Date])) != 0

      THEN [Cost]/(INT(DATEPART("month", [End Date])) - INT(DATEPART("month", [Start Date])))

      ELSE

      [Cost]

      END

       

      Even though dollar amounts should be allocated to February through June, if I filter on start date, I won't see those amounts. A similar question was asked before, but went unanswered.

       

      Data Allocation Across A Date Range (Trying to Interpret an Excel Equation)

       

      How do I capture dollar amounts in subsequent amounts?

       

      Thanks!