How to calculate difference / delta between 2 months in Crosstable

[Wee Loon Goh]

Hi,

I would like to know how to calculate the difference of value between 2 months in crosstable if the 2 months if selected by user.

The issue I have is when user choose the 2 months that is not continuous (example: Jan and March), the calculation forward as below will not be working. ( See Screenshot 1 and Screenshot 2 for comparison)

 

Sum([Total_USD]) - Sum([Total_USD]) OVER (Previous([Axis.Columns])) as [Changes $]

 

May I know if there is any other formula expression I can use for this situation

 

Thanks in advance for the help.

[Fabian Duerr]

You can use the aggregation methods "Difference" and "Difference %" in your cross table. When using "Difference" the formula should look like this:

Sum([Total_USD]) THEN [Value] - First([Value]) OVER (NavigatePeriod([Axis.Columns],0,-1))

Now, you could control the number of steps (here -1) with the user selection. From March to January it should be -2. But the better option would be to go to the settings of your axes and switch from "all data" to "current filtering only" for the evaluation of the axis expression. Then your filtered cross table will always compare to the neighbouring columns when the number of steps is set to -1