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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 :-)(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.