Analytical Need 

Calculate the time difference between two events in a fact table.
In the example, we want to measure how many days it took to complete each task in a shipment process.

 Modeling Challenge

1. Orders fact table has 3 fields: Order ID, Status, Date.
2. The order process is consisted of 3 steps: creation, packaging and shipment.
3. Each entity in the fact table has only the date of the step it records. 

Original Fact table:

Solution

After configuring a couple of added fields, we can use the Lookup function to bring in the date of the previous event in a single table. Next, we can simply calculate the difference in days using DayDiff.

Steps:

1. Rank all the events based on their date with partition by the Order ID:

2. Create key for current step by concatenating the Order ID and the Rank:

3. Create key for the previous step by concatenating the Order ID and the Rank of the previous step, which is the StepRank-1

4. Lookup the date of the previous step within the same fact table by matching on the previous step key and the step key:

5. Calculate the days difference between the date and the previous step date to get the duration of the task: