Downloads
21501.zip

Use the FoodMart Sales cube to write an MDX formula that counts the slope sign changes in a trend line of months. A slope sign change occurs when Sales changes from positive growth to negative or vice versa. Just for fun, find all the product brand names that have six slope sign changes in the months of 1997.

Listing A is rather long. I created two calculated measures: SalesDelta2 represents the sales change from the most recent time period to the current time period, and SalesDelta1 represents the sales change from two periods before the most recent time period. I then used the SlopeChangeMembers set to find all the time periods in which SalesDelta1 and SalesDelta2 move in different directions. The count of these time periods equals the number of slope changes.