cancel
Showing results for
Did you mean:
Highlighted
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 .

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Need help with conditional data aggregation

Hi,

This is the result i got.

Super User

## Re: Need help with conditional data aggregation

Hi,

You may download my PBI file from here.

Hope this helps.

9 REPLIES 9
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?

Frequent Visitor

## Re: Need help with conditional data aggregation

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

## Re: Need help with conditional data aggregation

Hi,

This is the result i got.

Frequent Visitor

## Re: Need help with conditional data aggregation

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

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

## 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

## Re: Need help with conditional data aggregation

Hi,

You may download my PBI file from here.

Hope this helps.

Frequent Visitor

## Re: Need help with conditional data aggregation

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

Thanks again!

Super User

## Re: Need help with conditional data aggregation

You are welcome.

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 218 members 2,215 guests
Recent signins: