Create a PQL formula to add to queries to see the yearly sales variance. To review PQL functions, click here.
The syntax of this formula will be:
([measures].[data Sales])-([measures].[data Sales],PrevMember([data].[dateKey year]))
Build a custom member in Formula, using two data points. The first data point should contain the Sales measure. Add a minus sign, and then add the second data point.
To the second data point, add the Sales measure, and then open the dateKey Year hierarchy. Instead of selecting a year from the Elements panel, click the tree icon next to the search tool.
Select Previous Member from the Elements panel.
Click the Pyramid button to preview the formula syntax. Then, save the custom member, or click the Quick Discovery button.
Open a new discovery and add: dateKey Year, Sales, and the new custom member.