Formula not allocating revenue to correct dates

I have got a data set with billings. I want to allocate the revenue by date so if a contract for 12,000 starts on 1/2/2018 and ends on 31/1/2019, the revenue allocated should be 12000/365 and only for the dates 1/2/2018 to 31/1/2019 inclusive. I've got to a certain point with the formula. However if you look at the attached file, the revenue is NOT entered for 1/2/2018 even though the contract starts on that date. Also the revenue entry continues past the start date plus 365 days. I'm now officially googly eyed looking at this formula so I need another few pairs of eyes on it. :-) would appreciate someone telling me what I am missing.. thanks as always..
Who is Participating?

pls try in Col F
=IF([@Value]<[@[Start date]],0,IF(AND([@[Start date]]<=[@Value],[@Subscription]=$I$1,([@Value]<=[@[Start date]]+365)),[@[Billed amounts]]/365,0))

Open in new window

ShumsDistinguished Expert - 2017Commented:

Try below:
=IF([@Value]<[@[Start date]],0,SUMIFS([Billed amounts],[Start date],">="&[@[Start date]],[Value],"<="&[@Value],[Subscription],$I$1)/365)
Matt NicholasBusiness AnalystCommented:
Hi agwalsh,

Remove the "=" operator within your first AND statement and revenue will generate on 01/02/2018.

IF(AND([@[Start date]]>=[@[End Date]]

Was this all you were looking for?

I would probably recommend adding a custom column in power query called 'contract end' which will simply be 'Start' + 1 year. Then have a new formula which basically states that IF date is within the 'Start' or 'contract end' column ranges, then 'revenue' / days in year
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

agwalshAuthor Commented:
@Rgonzo1971, you beauty!  Did exactly what I wanted. Thanks to you too @Shums. I'm attaching the file to show the outcome of the two different formulas.
agwalshAuthor Commented:
@Matt Nicholas - excellent idea. I had two randomish files so I used Power Query to get the dates in (as you noticed). I *knew* it was something with the dates I was missing but needed outside eyes to see it. Still have work to do with that formula because some of the recurring revenue types require a slightly different treatment but I'm just waiting for the user to get back to me to clarify.  Can you outline for me your  revenue days in year thinking? Thanks.
Matt NicholasBusiness AnalystCommented:
Hi agwalsh

Meant to say 'revenue per days in contract term (not in year)'

Since each day within a contract cycle has an equal portion of contract revenue (1 year contract = 100k, the 100,000 / 365 days = $273.97), I just thought that you could allocate revenue to any date that effectively fell within the contract start and end dates an equal portion of revenue.
agwalshAuthor Commented:
As always, EE excellent for helping to see the wood for the trees and offering a new and fresh perspective on a problem. Thank you :-)(
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.