Creating a previous month metric (transformation)

Transformations are schema objects, and a very powerful tool that MicroStrategy offers to modify the results of a metric, think about the classic "this month versus previous month" report. But it's not only that. Normally, when the SQL statement is generated, the fact table is joined to the dimension tables using the ID forms of the attributes on the grid.

The transformation allows altering the join path between fact and dimension by introducing an extra step. This extra step could be a helper table, or could be a calculation. Think about the SalesAmount in the FactResellerSales table: when we join the fact to the DimDate table on the OrderDateKey, we get the amount of sales for that particular day. By introducing a helper table we can alter the date so that we get the same number but with a different date, for example one month earlier.

We will see an example in a moment.

Getting ready

We need to create a helper table to calculate the previous...