This option requires that every date field have at least one record for every date bin in the view. In this example, every row is a month, therefore both [Order Date] and [Ship Date] need to have at least one order for every month.
- Choose one date field to create the date axis or headers. This example uses [Order Date]
- Right-click and drag [Order Date] to the Rows shelf
- In the Drop Field dialog, select MY(Order Date) with the blue # icon and click OK
- Right-click and drag [Order ID] to Text on the Marks card
- In the Drop Field dialog, select CNTD(Order ID)
- Right-click the header "Distinct count of Order ID" in the view and select Edit Alias…
- In the Edit Alias dialog, type in a new name and click OK
- Create a calculated field with a name like "Orders Shipped" with a calculation similar to the following:
MIN(
IF DATETRUNC('month', [Order Date]) = DATETRUNC('month', [Ship Date])
THEN { FIXED DATETRUNC('month', [Ship Date]) : COUNTD([Order ID]) }
END
)
9. Double-click [Orders Shipped] in the data pane to add the field to the view