The key to your calculation is the LOOKUP function, which lets you lookup values from other rows in the partition. I created a calculated field in the attached workbook called Comparative Sales
LOOKUP(SUM([Sales]), 0) - LOOKUP(SUM([Sales]), -4)
This calculated the difference between the current value, offset = 0, and the value at offset -4 (based on your example, you wanted to go from May back to January, which is an offset of -4).
I built an example in the attached workbook & the results look like this:
Hope this helps,
Comparative Sales.twbx 28.3 KB
Thank you Steve.
When I have to use this Comparitive Sales table calculation on a map or something, but still base the calculation off of the month-year, it doesn't seem like this calculation would work. Could you please let me know how to base this calculation off of month-year, but still plot this on a map or another graph which shows the comparative sales by region or some other dimension? right now, when I pull this field on the map, it says null, for all the regions.
Please post a packaged workbook with an example use case. I'm not sure I understand exactly what you are trying to do with the map.
I have attached the workbook:
State ID, Month, Sales.
I have calculated Comparitive Sales using some table calculations.
First tab plots comparative sales by month, the table calculation work fine.
Second tab: trying to plot the state from the second dataset while showing comparative sales from the first dataset. I have created a join on State ID. Here all the comparative sales values shows null.
The Comparative sales table calculation worked in the first example because the worksheet included data across multiple months. This made it possible to use the LOOKUP function to compare current month to 3 months ago.
Your map example is a different use case - you've taken out the time element (months) and added data blending.
I suggest taking smaller steps (maybe build an example without adding mapping & blending in one step). Try building a Viz that shows comparative sales in a line chart for each state, for example.
Sorry I can't be of more help on this, but I think having a deeper understanding of how the table calc works in the first example will get you closer to where you want to go.