Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My source data format (after unpivoting) looks like this -
PatientID | Status | Date
p234 | Active | 01-Oct-2018
p234 | Discharged | 12-Dec-2018
p234 | Dropped | 15-Feb-2019
p235 | Active | 11-Oct-2018
p235 | Discharged | 05-Jan-2019
p236 | Active | 17-Nov-2018
p236 | Discharged | 15-Jan-2019
p236 | Dropped | 18-Jan-2019
Basically, each patient goes through a journey of becoming active (in a support program), discharged and then finally dropped. What I need to build is a report that answers this business question - how many Active/Discharged/Dropped patients do I have in any given month, Jan-2019?
The desired output should look like this -
Oct-2018 | Nov-2018 | Dec-2018 | Jan-2019 | Feb-2019
Active 2 | 3 | 2 | 0 | 0
Discharged 0 | 0 | 1 | 2 | 1
Dropped 0 | 0 | 0 | 1 | 2
Total 2 | 3 | 3 | 3 | 3
Explanation -
1. 2 patients (p234 & p235) were active in Oct-2018 and then p235 is active in Nov-2018. Therefore, total active patients in Oct-2018 and Nov-2018 are 2 and 3 respectively.
2. Patient p234 gets discharged in Dec-2018, therefore for Dec-2018 Active = 2, Discharged = 1.
3. Next, patient p235 gets discharged on 5-Jan-2019. p236 gets discharged on 15-Jan-2019 and subsequently dropped on 18-Jan-2019. Therefore, for Jan-2019 Active = 0, Discharged = 2, Dropped = 1. Note - if a patient's status change during the same month, for example p236 gets discharged and dropped in Jan-2019, the patient will be counted as dropped. Similarly, for instance, if a patients is active and then discharged during the same month then the patient will be counted as discharged for that month.
Please help! Please download the PBIX file from here. I have included the output as a table named Desired Output which I manually typed :).
Thanks
Solved! Go to Solution.
Hi,
This is the result i got.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
This is the result i get. I do not quite agree with yoru results in Feb 2019. I think for Feb 2019, discharged should be 0 and dropped should be 1. I have shown patient ID's there to prove my point. Are you OK with this result?
Hi @Ashish_Mathur,
I agree with your result till Jan-2019 (there were some mistakes in my previous Desired Output table). So, for Jan-2019 - Active = 0, Discharged = 2 (p234 & p235), Dropped = 1 (p236) and this is correct. But in Feb-2019, p234 gets dropped so will get added to Dropped count for Feb-2019. And therefore for Feb-2019 - Active = 0, Discharged = 1 (p235 is carried forward from Jan-2019) and Dropped = 2 (p236 & p234). I hope this makes sense.
I have corrected the PBIX file, link is here.
Thanks
Hi,
This is the result i got.
Hi @Ashish_Mathur, could you please post the PBIX file or the solution steps? Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
Hi,
Sorry for the delay in replying. I am travelling. Please allow me time until Sunday to share my solution.
Thanks @Ashish_Mathur! This is the correct result. Please do share the PBIX file.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |