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