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

# Allocating amounts across a date range in Salesforce

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!

• ###### 1. Re: Allocating amounts across a date range in Salesforce

So what I'm hoping to see is in the example below. What I end up getting is actually something else, though.

This is the dataset: (same structure as an opportunity line item in Salesforce, with start and end date in the same row)

This is what I'm hoping to get:

This is what I actually get:

There are some examples around, most notably these two, but these count records in a date range rather than summing the amounts allocated to each period. I've used both method #2 in the Tableau article and Ćukasz Majewski's sample workbook, but I get different counts. I also don't really understand WINDOW_SUM function having never used it before, but I'd prefer using that as opposed to using a second data source

If anyone can shed some light, it would be very much appreciated. Thank you!

• ###### 2. Re: Allocating amounts across a date range in Salesforce

Hi Dani

I will walk you through this but this is the result

I have attached a T10.1 workbook

This is the formula for the difference in months >>DATEDIFF('month',[Start Date],[End Date])+1

This is the calculation for the average >>sum([Cost])/sum([month difference])

Let me know if this helped

Jim

1 of 1 people found this helpful
• ###### 3. Re: Allocating amounts across a date range in Salesforce

Hi Jim, this isn't what I'm looking for.

For example, if there's a payment that covers two months, January and February, I want to see that cost as two separate rows, one in January and one in February, with the cost amount equal to the total cost divided by the number of periods. I can only capture the cost in month of the start date, but not any subsequent periods.

Example record:

Start Date; End Date; Cost; Name

Jan 2015; Mar 2015; 30000; Event1

Desired output:

Month; Name; Cost

Jan 2015; Event1; 10000

Feb 2015; Event1; 10000

Mar 2015; Event1; 10000

END

Actual output:

Month; Name; Cost

Jan 2015; Event1; 10000

END

• ###### 4. Re: Allocating amounts across a date range in Salesforce

Hi Jim,

I have been following this particular question since beginning and I used another approach as seen in attached. I am stuck towards the end in building some kind of logic to arrive at what he wants.  I am now giving up on this. Pl have a look at the Screenshot and attached if you can build it up from here to help him.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 5. Re: Allocating amounts across a date range in Salesforce

Thanks for looking at this -

I spent some time looking at your work and also trying a couple of different approaches.

I finally concluded that to get the view he wants we would would need a single record in the data base to act like 2, 3, 4 records depending on the date range in the record. (if that makes sense).  I don't know any way to do that without restructuring the data source.  I looked at scaffolding but that doesn't seem appropriate.

Maybe someone else would know.

Jim

1 of 1 people found this helpful
• ###### 6. Re: Allocating amounts across a date range in Salesforce

Hi Dani,

This is a real tough nut. I had used a different approach pivoting as well as consulted Jim, so what I could come up finally is that in the following view you are able to access the Service period (From Start to End Month), Actual Month of Service and Cost of Service per Month.

Though I am not able to get in between months, I hope this Helps as well as Jim's Solution. Don't hesitate to mark Helpful to both me and Jim as this was really tough for us to come up to this point too and involved lot of thinking.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 7. Re: Allocating amounts across a date range in Salesforce

Here is attachment in 10.2

2 of 2 people found this helpful
• ###### 8. Re: Allocating amounts across a date range in Salesforce

Thanks Deepak

Very elegant - see what you did -

Jim

1 of 1 people found this helpful
• ###### 9. Re: Allocating amounts across a date range in Salesforce

Thanks Jim