cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
msingh2019 Frequent Visitor
Frequent Visitor

Need help with conditional data aggregation

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 Smiley Happy.

 

Thanks

@Ashish_Mathur 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Need help with conditional data aggregation

Hi,

This is the result i got.

Untitled.png

View solution in original post

Super User
Super User

Re: Need help with conditional data aggregation

Hi,

You may download my PBI file from here.

Hope this helps.

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Need help with conditional data aggregation

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?

Untitled.png

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with conditional data aggregation

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

Super User
Super User

Re: Need help with conditional data aggregation

Hi,

This is the result i got.

Untitled.png

View solution in original post

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with conditional data aggregation

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

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with conditional data aggregation

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

Super User
Super User

Re: Need help with conditional data aggregation

Hi,

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

Super User
Super User

Re: Need help with conditional data aggregation

Hi,

You may download my PBI file from here.

Hope this helps.

View solution in original post

msingh2019 Frequent Visitor
Frequent Visitor

Re: Need help with conditional data aggregation

Hi @Ashish_Mathur,

 

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

 

Thanks again!

Super User
Super User

Re: Need help with conditional data aggregation

You are welcome.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 218 members 2,215 guests
Please welcome our newest community members: