model view
1 Topic
Please Urgent Help with Creating Period blocks from Start Date Column
Hello, Please, I desperately need help with any tips or recommendations on how to target this problem. I have one requirement in a cencus report I built to add actuals to target services of patients. What I mean by this is, if I have a patient who is required to have their services done 2 times monthly, I would hope from the start date to 30 days from that start date, they have been attended to, 2 times giving me 100% of my actual to target. So again, if my expected services to be done is set to be 2 services monthly, my actual services in that time period from the start date e.g. 1/1/2022 - 1/31/2022 is 1, then that I would have been attended to just 50% of the time. Is there a way anyone could advise me how to target this problem given the below data, and if so, would you recommend some ideas please? I really need help with how I can build some of this period blocks so if its - monthly, +30 days from start date - weekly, +14 days from start date -bi-monthly, +60 days from start date - quarterly, +90 days from start date and so on I had to model both of these fact tables in power bi. I can also perform a join in the database to merge all to one, of which I plan to do. Its just a bit challenging considering the frequency table has a start & end date, and the service table has just a calendar date. I guess I can join both tables on their patient_id and service_date where it falls between the from and to date columns in the frequency table. But please advise, anything would be greatly appreciated. I really need inputs on how to get this one visual out. THANK YOU so much in advance. Sample raw data frequency_table: patient_id patient_name start_date end_date expected_frequency frequency_code frequency_type 1001 Sara S 6/18/2020 12/31/2020 2 3 Monthly 1001 Sara S 1/1/2021 3/1/2021 1 4 Bi-Monthly service_table: is_attended code 1 for Yes, 0 for No. patient_id patient_name date is_attended 1001 Sara S 6/18/2020 1 1001 Sara S 6/30/2020 1 1001 Sara S 7/18/2020 1 1001 Sara S 7/31/2020 0 1001 Sara S 8/18/2020 1 1001 Sara S 8/31/2020 0 1001 Sara S 9/18/2020 1 1001 Sara S 9/30/2021 1 1001 Sara S 10/18/2021 1 1001 Sara S 10/29/2021 1 1001 Sara S 11/18/2021 0 1001 Sara S 12/5/2021 0 1001 Sara S 12/18/2021 0 1001 Sara S 12/27/2021 0 1001 Sara S 1/1/2021 1 1001 Sara S 2/1/2021 0 1001 Sara S 3/15/2021 0 Expected Results: to_frequency_date is a sample column for the period date from the frequency "start_date". patient_id patient_name from_date to_frequency_date expected_frequency Actual frequency_type % Actual to Target(freq) 1001 Sara S 6/18/2020 7/17/2020 2 2 Monthly 100% 1001 Sara S 7/18/2020 8/17/2020 2 1 Monthly 50% 1001 Sara S 8/18/2020 9/17/2020 2 1 Monthly 50% 1001 Sara S 9/18/2020 10/17/2020 2 2 Monthly 100% 1001 Sara S 10/18/2020 11/17/2020 2 2 Monthly 100% 1001 Sara S 11/18/2020 12/17/2020 2 0 Monthly 0% 1001 Sara S 12/18/2020 12/31/2020 1 0 Monthly 0% 1002 Sara S 1/1/2021 3/1/2021 1 1 Bi-Monthly 100% Please let me know if you have any questions, happy to clarify.Solved524Views0likes1Comment