Distance, in days, between Date members in analysis cube

I am using BIDS to create an OLAP cube, and I have a hierarchal date structure that includes years, quarters, months, and dates.  I want to build a calculation with enough savvy to plug in the appropriate growth factor given the length of time with which I'm working.  For example, if I expect sales to grow by 10% in a year, I would like to test for 10% if I'm working with years, 10%/4 if I'm working with quarters, 10%/12 if working with months, and 10%/365 if working with days.  I understand, theoretically, what I need to accomplish: find out the number of days between the current and previous member and multiply my percentage (i.e., 10%) by that number of days (i.e., 365, 120, 30, 1) over 365.  As a practical matter, I am stumped on the implementation.  
Any ideas?  Thanks, ~Peter Ferber
PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?

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

x
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.

omegaomegaDeveloperCommented:
Hello, PeterFrb,

Sorry, I don't know anything about BIDS or OLAP cubes, so can't offer any guidance on the "practical" side.

But on the "theoretical" side, please be aware that growth functions are typically exponential, not linear.  For example, a function that grows at a rate of (10%/356) per day does NOT give the same result as one that grows at 10% per year.  (For my money, I'd prefer the former.)

If you can show some code, someone (even those without a clue about BIDS/OLAP) might be able to help with the practical side of the implementation.

Cheers,
Randy
0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
An excellent point, Randy, and you're right!  I thought about this after I put out the request.  I believe the question still revolves around how to tell the number of days between a current and previous member, relative to the size of that member, but I concur that the resultant math will have to be more subtle than what I had originally conceived.  I thank you for the added nuance you have added to the problem, and anyone coming up with a solution should factor this important information in.  You're definitely getting some credit when points are handed out.
Best, ~Peter
0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
OK, I have the equation that will yield the value I want, which I've attached as an image file.  I've also given a specific example, given that the percent growth for the year is 10%, and the current member is the month of June.  A quick check in Excel shows that the numbers come out properly.  The next practical step is to represent this as an MDX query.
Best, ~Peter

Generic-PercentEquation.bmp
0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I have solved this problem on my own and will, at some point, publish my findings.
0

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
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
.NET Programming

From novice to tech pro — start learning today.