Power BI Challenge 5 - Optical Data

It is a great idea to have these challenges, allowing to test PBI skills in different real life scenarios. I am happy with deadline extension as I only have weekends and few hours on weekday evenings.

DNA resources were very useful in creating the report: I used a colour generator, DAX formatter and a date table function.  However, I made start and end date dynamic, keeping in mind that Optical Manager will have more data in future. An extra year was allowed to show upcoming appointments.

Age was calculated in Power Query and will be recalculated every time the report is refreshed.

I thought that Smoker, Driver, Private and Subsidised values can change over time. For that reason, I used a measure returning value relevant for the last appointment date

The challenge for me was how to show missed and upcoming appointments in a visual. Initially I created a measure, but I could only use it in Patient Details table. Finally, I decided to have a calculated table and an active relationship with a fact table and an inactive relationship with a date table

Ideally I wanted to have a name filter on Patient Details page and a drill through button on Appointments page allowing to drill through to details. However, I could only have either the button or the filter. I went for the button.