I’m new to Power BI and I need to learn how to calculate a count of events in one table based on if the event date is before or after a specific date with another table. I thought the E-DNA youtube clip, “Calculate The Occupancy Days Per Month” may work for me (with a couple of alterations using EDate etc), but the problem I have is between two tables with a many to many connection.
Our service receives referrals and I’m wanting to show the impact of our service by a 12month pre and post comparison of how often our patients present to emergency or are admitted etc. Each referral has its own unique ID and a First Appointment date which is used as the reference date for the pre and post comparison. This information is in the Referrals table. Each referral has a single patient associated to it and each patients has their own unique ID. However, over time we now have multiple referrals for individual patients (i.e. 1 patient can have up to 4 referrals).
The service events (i.e. emergency presentations) sit in their own table where each event is associated with a single patient ID. The only association between the Referral table and the Service Event table is the patient ID; hence a many to many relationship due to patient’s having multiple referrals.
I have created a patient ID table which contains a distinct set of patient IDs. However, I need to do the comparison on the Referral ID and its associated First Appointment date, not just the distinct patient ID; I’m not sure how to overcome the many to many relationship.
The outcome I’m looking for is:
Currently I’m undertaking performing the pre and post calculations in excel and bring in the results to PowerBI, which is a laborious and additional process which I’m hoping to automate in PowerBI, which will also allow for much deeper analysis with associated event specific details.
Any guidance will be greatly appreciated.