Sales Growth Calculation on Year-to-date basis
I am trying to build a report for calculating the Year-on-year sales growth. I am getting the cumulative for the entire year. However, I am interested to visualize this on a YTD basis ( till July or till date ) . Kindly guide me here. The screenshot is attached for reference. Thanks.
BI Office parameters, coupled with some MDX functions will give you what you need.
First create a parameter that will contain all the months (or other time periods) you want to be able to select:
you can quickly and easily create a YTD custom member in your date dimension, right click on the parameter and use the slide off menus from the date element tree.
NOTE: For this to be available, the Dimension needs to be declared as Time and the Date hierarchy will need to include named levels, Year, Quarter, Month, Date etc.:
Selecting the parameter and the YTD member to display on the grid, will result in the following screen, where you can pick a month and it will show that month and its YTD value:
In order to build out the YTD Year on Year % growth, we need the YTD value for the same month the previous year. this needs to be driven again by the parameter, but there is no slide off to build this out, so we will need to use the Custom Member MDX editor.
Copy the YTD member, then right click on it and choose Edit.
We then need to add an MDX function called PARALLELPERIOD. This will take the member selected (the parameter, [Date].[Month Dates].[!@Month@!]), navigate up to the Year level of our hierarchy ([Date].[Month Dates].[Year], take a step back (1), then travel back down the hierarchy to find the same month at the month level for that year.
Finally, for the % growth we need to take this year's YTD, subtract Last Year's YTD and divide by Lat Year's YTD. I've used complete code here for clarity, but of course, you can use the intermediate members, YTD and Last Year YTD in the calculation:
Note the formatting of 0.00% to give the correct view of the value.
The final result displaying the selected parameter, YTD, Last Year YTD and % Growth:
Hope that helps!
PS If you are using a non time hierarchy or a specialised financial calendar, you can use the MDX Function PERIODSTODATE instead of YTD.Reply