Use Dates as Measures

Often you have dates in your facttable that represent different stages of a certain transaction.

This could be Order Date and Shipped Date as a typical example, but there are many others.

In this example we have both Order Date and Shipping date in our fact table, and we want to create measures based on these to columns.

So first we drag OrderDate to the Measures folder on the left hand side.

Now we make sure the Measures folder is expanded, and click on the new Measure OrderDate to work with it.

The aggregate type is by default set to DistinctCount, but this can be changed by clicking the dropdown.

In this case we select MAX, which means that the measure will represent the highest date in a certain context.

We repeat the action with ShippedDate - and make sure it's also set to MAX in the aggregate type.

Now we can work with these measures in the frontend:

We could do a calculation called Days from order to shipment (subtracting those two) and hide the original measures.

Other typical aggregate types you might want to use could be MIN or AVG.