It was Lorna’s turn to set the challenge this week, and she opted for a gentle workout to soothe us into the Christmas holidays – I certainly appreciated it as I am super busy and super stressed trying to finalise Christmas presents and preparations!

I built this with 4 calculations in total. Let’s start by just building out a tabular view of the data, so we know what we’re looking for.

Add Customer ID and Order Date set to be a discrete (blue) exact date onto Rows.

To start with, we want to capture, against each customer, the earliest order date

First Purchase Date

{FIXED [Customer ID]: MIN([Order Date])}

Add this into Rows as a discrete exact date.

You can see that for every row associated to the same customer, the First Purchase Date matches the first row.

Now I want to identify the row that represents the first order for each customer

Order is first order

[Order Date] = [First Purchase Date]

Add this onto Rows and we can see the first row against each customer is True, while the rest are False.

I can now use this to identify the date of the 2nd order for each customer – it is the earliest date where the order isn’t the first order

Second Purchase Date

{FIXED [Customer ID]: MIN(IF NOT [Order is first order] THEN [Order Date] END)}

Add this onto Rows as a discrete exact date, and you can see that every row associated to the customer, now has the date that matches the 2nd Order Date listed.

Remove Order Date and Order is first order from the Rows and we have 1 row per customer with the 2 dates we’re interested in.

From this we can work out

Quarters Since First Purchase

DATEDIFF(‘quarter’, [First Purchase Date], [Second Purchase Date])

Add this onto Text, so you can validate the result.

Now we can build out the matrix.

On a new sheet add First Purchase Date to Rows and set to be at the discrete (blue) quarter level. Then add Quarters Since First Purchase to Columns and change to be a discrete Dimension.

This already gives us the shape we’re after. Right click on the Null value in the column heading and select Edit Alias and update the word to Lapsed.

Add Customer ID to the Colour shelf, and the click on the pill and change to be a Measure of Count Distinct. This will change how the viz is displayed. Adjust the colours accordingly.

Finally update the Tooltip to match the required text, and adjust the formatting so there are pale grey thin dotted row and column dividers on the pane only, but not the header.

Add the viz to a dashboard, and you’re done! My published viz is here.

Happy vizzin’!

Donna