Patient Tracker and Package Tracker

[sanjurk_1506]

Hi,

I have two  excel sheets one in which patient attendance is tracked with which therapist has been attended. Another sheet that says the type of package that the patients has bought. 

Every day I need to calculate the revenue by each Therapist. I have attached both the sheets, to show the type of data that is being generated from the system. There are few things to note here.

The cost per package for each therapist differs. 

For the same patient Speech Therapy Package will be different price and Occupational Therapy Price will be different price. 

The package price also differs for each patient on the day of purchase of the package from and to mentioned below

Patient Attendance Tracker 

Patient Name	Patient ID	Therapist	Department	Date
Shyam Hani	153	Ryaan	Occupational Therapy	02/10/2024
Shyam Hani	153	Ryaan	Occupational Therapy	04/09/2024
Shyam Hani	153	Ryaan	Occupational Therapy	06/09/2024
Shyam Hani	153	Sanju	Speech Therapy	02/10/2024
Shyam Hani	153	Sanju	Speech Therapy	04/09/2024
Shyam Hani	153	Sanju	Speech Therapy	05/10/2024
Shyam Hani	153	Sanju	Speech Therapy	06/09/2024
Shyam Hani	153	Sanju	Speech Therapy	07/09/2024
Meera Hasan	152	Sanju	Speech Therapy	09/10/2024
Meera Hasan	152	Sanju	Speech Therapy	09/10/2024
Meera Hasan	152	Sanju	Speech Therapy	10/08/2024
Meera Hasan	152	Sanju	Speech Therapy	11/09/2024
Meera Hasan	152	Sanju	Speech Therapy	11/09/2024
Meera Hasan	152	Sanju	Speech Therapy	11/10/2024
Meera Hasan	152	Sanju	Speech Therapy	11/10/2024
Meera Hasan	152	Sanju	Speech Therapy	12/10/2024
Dev Mani	112	Sanju	Occupational Therapy	01/10/2024
Dev Mani	112	Sanju	Occupational Therapy	02/10/2024
Dev Mani	112	Sanju	Occupational Therapy	04/10/2024
Dev Mani	112	Sanju	Occupational Therapy	08/10/2024
Dev Mani	112	Sanju	Occupational Therapy	09/10/2024
Dev Mani	112	Sanju	Occupational Therapy	10/09/2024
Dev Mani	112	Sanju	Occupational Therapy	10/10/2024
Dev Mani	112	Ryaan	Occupational Therapy	11/09/2024
Dev Mani	112	Ryaan	Occupational Therapy	11/10/2024
Dev Mani	112	Ryaan	Occupational Therapy	12/09/2024
Dev Mani	112	Ryaan	Occupational Therapy	01/10/2024
Dev Mani	112	Ryaan	Occupational Therapy	04/10/2024
Dev Mani	112	Ryaan	Occupational Therapy	08/10/2024
Dev Mani	112	Ryaan	Occupational Therapy	10/10/2024

Patient Price Tracker

Patient Name	Therapist	Patient ID	Package From	Package To	Package Price	Package
Shyam Hani	Sanju	153	Wednesday, 2 October 2024	Wednesday, 4 September 2024	100	Speech Therapy
Shyam Hani	Ryaan	153	Wednesday, 2 October 2024		0	Occupational Therapy
Meera Hasan	Sanju	152	Wednesday, 9 October 2024	Saturday, 12 October 2024	200	Occupational Therapy
Dev Mani	Sanju	112	Tuesday, 1 October 2024	Tuesday, 8 October 2024	300	Occupational Therapy
Dev Mani	Ryaan	112	Saturday, 27 July 2024	Tuesday, 27 August 2024	400	Occupational Therapy

[Anonymous · Accepted solution]

Hi @sanjurk_1506 ,

Do you want to get package price for each recored in Patient Attendance Tracker? If so , you could add a caculate column for Patient Attendance Tracker. I'v made a test for your reference:

Package Price = CALCULATE(
    MAX('atient Price Tracker'[Package Price]),
    FILTER(
        'atient Price Tracker',
        'atient Price Tracker'[Patient ID] = 'Patient Attendance Tracker'[Patient ID] &&
        'atient Price Tracker'[Package] = 'Patient Attendance Tracker'[Department] &&
        'atient Price Tracker'[Package To] >= 'Patient Attendance Tracker'[Date] &&
        ('atient Price Tracker'[Package From] <= 'Patient Attendance Tracker'[Date] || IsBlank('atient Price Tracker'[Package From]))
    )
)

Best Regards,
Bof

[sanjurk_1506]

@Anonymous 

This is working out, but is there a change that could be done that, instead of 0 I have put 200 on the second line below.

What I meant by the blank is that the package starts from Wednesday, 2 October 2024, but the end date is nto know, so until then the package price will 200.

The blanks mean that the end date of the package is not known - it will be either when the client stops coming.

atient Name	Therapist	Patient ID	Package From	Package To	Package Price	Package
Shyam Hani	Sanju	153	Wednesday, 2 October 2024	Wednesday, 4 September 2024	100	Speech Therapy
Shyam Hani	Ryaan	153	Wednesday, 2 October 2024		200	Occupational Therapy

[dk_dk]

Hi @sanjurk_1506 

You could do the following:

Transform the Patient Price Tracker as per this blog post: https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Creating new rows for the dates between the "package from" and "package to" columns, for each original row. You can call this new column Date.

In both tables, you need to create a custom ID column in format like this, or similar:

<patient_id>_<package/department>_<therapist>_<date>

so the first row in your first table, the value will be:

153_Occupational Therapy_Ryaan_02/10/24

and this should be the same but with Speech Therapy for the first row in your second table, after you add the custom column for the dates and expand it to rows

Then you can merge the two queries together based on this ID column, and expand only the Package price. (if the package price is price per day, you dont need to do anything. if the package price is price for all the days, then you need to divide the package price by the number of days that is the duration of the package (you can calculate this as a separate custom column in the second table based on the From and To dates).

I hope this helps, let me know if you run into any issues and I will try to clarify further.