Forum Discussion

sanjurk_1506's avatar
sanjurk_1506
Regular Visitor
1 year ago
Solved

Patient Tracker and Package Tracker

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 d...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

dk_dk's avatar
dk_dk
Icon for Super User rankSuper User
1 year ago

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.