Hi everyone,
Here’s my submission for Challenge 5 - Optical Data
Thought I would take the scenic route and include a Menu page, although this can be omitted.
It leaves the user with two routes into the actual report.
The main one being the “ Client Action Centre ” when you click on it (or right-click in Desktop) a page navigation action will take the user to that report page.
In the top of the screen there are buttons (based on bookmark actions) to zoom into a subset of customers (contact groups) to get things done quickly, additional filters can be set with the slicers on the left hand side.
Reschedule : Shows all customers 1-30 days overdue, it also suggests a new appointment date.
Remind : Shows upcoming appointment due dates in the next 0-30 days. Customers can actively be contacted to make an appearance 
. Based on their age it also suggests a date for a follow up appointment after that.
Invite : Shows upcoming appointment due dates in the next 31-60 days. Letters can be send to inform customers to expect a phone call to schedule an appointment.
Inquire : Shows all customers 31-180 days overdue, it also suggests reaching out for new appointment or to check client status if they missed a due date twice in a row.
Promote : Shows all customers >180 days overdue, you might still want to include them in marketing but no longer actively pursuit them…
At the moment the logic for these actions is arbitrary of course but can easily be updated to suit Penny’s actual wishes so she can then share this with the customer contact team.
The column chart at the bottom of the screen show’s the number of Appointment due dates within a 10 week window, all blue bars are future dates. This interacts with the Period slicer so to get quick insight in expected traffic based on Appointment due dates.
I’ve used the Dynamic Date Range Slicer M code you can find in the M code Showcase category on the forum and updated the time-frame bins better suiting this scenario.
Based on the client’s age at that point in time I calculated a Next Appointment- and Suggested New Appointment due date in Power Query. First time I ever created a recursive function in M so to calculate the next appointment due date for a date after today called: fxNextAppointment , that was fun!
In the end I decided to handle missed due dates differently so didn’t use it but for those of you who are interested, here’s the M code:
(myDate as date, dateDOB as date) as date =>
let
BufferedInterval = Table.Buffer(Interval),
curDate = Date.AddMonths(
myDate,
Table.SelectRows(
BufferedInterval,
(BT) =>
BT[Age] = Number.RoundDown(Duration.TotalDays(Date.From(myDate) - dateDOB) / 365)
){0}[Interval in Months]
),
Result = if Date.DayOfWeek(curDate) = 6 then Date.AddDays(curDate, 1) else curDate
in
if Result >= Today
then Result
else @fxNextAppointment(Result, dateDOB)
How does this fxNextAppointment function work?
- The function requires two parameters of type date.
- Loads a query called Interval into memory, this contains age ranges and interval in month values.
- Adds the number of months to the initial myDate parameter based on the age at that point in time and storing it in a variable called curDate.
- Checks if the curDate returns a Sunday if it does, it adds a day so it returns a Monday instead, storing it in a variable called Result.
- Next checks if the Result is equal to or after Today’s date
- If it is it returns the Result else the query calls itself and passes the Result to the myDate parameter to start the cycle all over again…
.
The “ Client Detail ” page is only accessible via drill through from the “Client Action Centre” page as noted in the bottom left corner of the screen. How that works is visually explained by hovering over the Table (?) tooltip via a GIF image in the background of the tooltip page.
Clicking on the Image/Title in the left upper corner will bring the user back to the “Client Action Centre” page and the hamburger menu in the bottom right corner to the “Menu” page.
On top it shows the Action messages for the selected Client and the current date (which isn’t up to date in our static POC sample data set). In multi-line cards on the left and right it shows all requested client details and also how many times this client visited, who saw the client last and how many flagged measurements have been recorded, so they can be examined more closely.
.
Since I attempted to incorporate visual ques for datapoint review, I also included an “Explore Flagged Clusters” page.
In certain circumstances rapid change in eye sight can be considered “normal”, that makes this part really tricky therefore I decided to base these flags on the direction of a vector, hoping to identify V shapes for example because that could be suspect…
The logic for the 1- and 2 point check is all done in Power Query because there’s a maximum of 10 data points I didn’t extend this further but we can add more levels if that is deemed necessary. Also wanted to give the user the ability to fine-tune threshold values so to optimize the results - that’s done via parameters. An explanatory page can be viewed by clicking on the “More about Flags” button.
This is by no means an exact science of course, think the best you can hope for is identifying clusters either in time or by optician and in that case maintenance/calibration of equipment or training is advised for example. In my view a heat map is the best way to visualize that.
.
All in all the DAX measures are pretty basic except for a segmentation pattern and even though I kept it simple I think you can get a lot of quick insights from this report.
Enjoyed working on this one and look forward to seeing all your entries…
.
UPDATE
@BrianJ was so kind to publish the report for me so here is the web link for all explorers among you.
