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?
 
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I have solved this problem on my own and will, at some point, publish my findings.
0
 
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
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.