Best way to measure working days between latest and second-latest order

[Keith_Fisher]

Hi,

I have a need to report on the number of working days between two events that will happen to a person. The dates of these events will be recorded in the same column of a table each time (creating a new row for each event per person). I need to know if the latest event happened to a person within 14 working days of the previous event. I then need to be able to summarise all of the latest events and show in a visualisation the number of people that had events happen within 14 working days of the previous one, compared to the number of people whose previous event happened more than 14 days prior to the last one.

I have managed to achieve this modelling while using the Wide World Importers DW database using the Fact.Orders and Dimension.Customer tables as surrogates. My steps were:

1. create a date table with a column for a working days flag (1 for work day, 0 for weekend/holiday)
2. in Power Query reference the Order table and then group/filter it on max order date per customer (removing duplicates rows and unnecessary columns as well)
3. repeat step 2 but this time, filter out the original max order dates per customer, then re-group and filter for the 'new' max order dates (to give me the previous order date to the most recent)
4. merge the two order dates from these referenced tables into the Customer table (so two new columns there, one for 'max order date' another for 'last but one order date'
5. create a calculated column on the Customer table that counts rows on the date table between the last and second-to-last order dates, where the working days column = 1
6. then created another calculated column that checks if the number of working days is lower or higher than 14 days.

All of this gives me the output that I need, but it has a lot of steps and I am wondering if anyone can think of ways to improve performance and efficiency, or combine steps?

Edit - this is the output I have achieved - shows the customer name, last two most recent order dates and the working days between them, as well as a donut chart showing proportion in asl v outside asl:

I used the WideWorldImportersDW database - easily available as a sample from Microsoft.

[v-shex-msft]

HI @Keith_Fisher,

According to your description, it seems like a common date range analysis requirement based on multiple date fields. If that is the case, I'd like to suggest you take a look at the following link 'start date', 'end date' part:

Before You Post, Read This 

Regards,
Xiaoxin Sheng

[parry2k]

@Keith_Fisher yes use dropbox/onedrive/google drive

[parry2k]

@Keith_Fisher It will be much easier to understand if you put sample data in an excel file and expected output. You have provided a great explanation but hard to follow without looking at the expected output.

[Keith_Fisher]

thanks for the advice, this is my first post on the board 🙂

maybe I'm being dense but how can I attach a spreadsheet - I've embedded the screenshot of my output but can't put an xlsx in in the same way, and there seems to be no 'attachment' option here. Do I need to put the spreadsheet in dropbox and embed a public link or something? I must be missing smoething...

Keith