Jump to content

How to calculate difference / delta between 2 months in Crosstable

Wee Loon Goh

Recommended Posts


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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...