Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lutur
Frequent Visitor

Deriving Customer churn from data alone

I have a table which records the resources working(nurses or attendants) in patients homes. Each day they work is recorded as a shift. Patient may have multiple resouces working in their homes on a given day. Resources work for periods of time - for a day or for many months at a stretch. They work on holidays too 

 

I want to calculate the shift increase/decrease in the following way- with respect to the previous day and another day (say End of Previous month)

 

1. New Patient: Where a Patient didnt exist in the table, and so a new patient-resource combination is considered New Patient

2. Ongoing: Where the patient exists on the table, AND the number of shifts on the preceding day or period equals to the number of shifts on that day, we mark the shift/s as ongoing

3. Reduce: Where the patient exists on the table in preceding day/period, AND Where the number of shifts for a patient has reduced from that previous period. However the Patient has atleast one shift on that day. The shift that is continuting is marked as Ongoing while the shift that has stopped is marked as reduced

4. New Shift: Where the patient exists on the table in preceding day/period, AND Where the number of shifts for a patient has increased from that previous period. The shift that is continuting is marked as Ongoing while the shift that has increased is marked as New Shift (This is the reverse of Point 3 - Reduce)

5. Attrition: When all shifts for the member are zero on that day, while there were one or more shift for that patient in the preceding day or period (This is the reverse of Point 1-New Patient)

6. Replace: Where the number of shifts for a patient remains the same, but the resource ID working there changes from the preceding day, mark the shift as replace

 

I am a newbie, but after a few weeks of trying to use summarise columns, inserting calendars, etc... I am at square one... Is what i am asking for doable, and how?

 

I can upload a truncated excel file... but dont know how to send...

Thanks in advance everyone...

Screenshot (1).png

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

This is quite complicated. I don't know if it can be done in DAX, but I created something in Power Query in this Excel file.

(shared from my OneDrive).

 

With regard to your remark "another day (say End of Previous month)" I created:

a parameter EndOfPeriodUnit as one of the values from the list PeriodUnits with days, weeks, months, quarters, years

a parameter NumberOfPeriods for the number of periods to go back

a function GetPreviousDate to get the previous date, based on a date (Date_Worked) and the 2 parmeters mentioned above).

 

You can edit the parameters via the option "Manage Parameters"  - "Edit Parameters" on the Home tab of the Query Editor.

After that, you need to refresh the query to get the results based on the new parameter values.

 

Query CustomerChurn should do what you want it to; I added some explanatory comments; you can see these in the Advanced Editor.

 

Probably this is not yet the final result. If you think we're on the right way and if you are confident that you will be able to manage it yourself (eventually), then we can proceed and apply any refinements.

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

This is quite complicated. I don't know if it can be done in DAX, but I created something in Power Query in this Excel file.

(shared from my OneDrive).

 

With regard to your remark "another day (say End of Previous month)" I created:

a parameter EndOfPeriodUnit as one of the values from the list PeriodUnits with days, weeks, months, quarters, years

a parameter NumberOfPeriods for the number of periods to go back

a function GetPreviousDate to get the previous date, based on a date (Date_Worked) and the 2 parmeters mentioned above).

 

You can edit the parameters via the option "Manage Parameters"  - "Edit Parameters" on the Home tab of the Query Editor.

After that, you need to refresh the query to get the results based on the new parameter values.

 

Query CustomerChurn should do what you want it to; I added some explanatory comments; you can see these in the Advanced Editor.

 

Probably this is not yet the final result. If you think we're on the right way and if you are confident that you will be able to manage it yourself (eventually), then we can proceed and apply any refinements.

Specializing in Power Query Formula Language (M)

Thanks Marcel

 

This would take me a couple of days to understand. Let me get back to you..

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.