Need help with conditional data aggregation

[msingh2019]

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

@Ashish_Mathur 

[Ashish_Mathur · Accepted solution]

Hi,

This is the result i got.

[Ashish_Mathur]

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?

[msingh2019]

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

[Ashish_Mathur · Accepted solution]

Hi,

This is the result i got.

[msingh2019]

Hi @Ashish_Mathur, could you please post the PBIX file or the solution steps? Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[msingh2019]

Hi @Ashish_Mathur,

Thanks a lot for your help. Your solution works perfectly!

Thanks again!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Sorry for the delay in replying.  I am travelling.  Please allow me time until Sunday to share my solution.

[msingh2019]

Thanks @Ashish_Mathur! This is the correct result. Please do share the PBIX file.