Looking to use Power Query Advanced Editor (or any other appropriate excel tool) for the following:
Looking use code to add a column to this table ("Meets Numerator Criteria") to indicate (utilizing 0 or 1) if the unique patient number satisfies the numerator medical visit criteria outlined below (utilizing a measurement period of 1/1/2020-12/31/2021). If all MedicalVisits per unique patient satisfy numerator criteria, all rows per unique patient number show as 1 in column "Meets Numerator Criteria", ELSE 0 :
Numerator Criteria (All bold statements must be true to satisfy numerator = 1):
Occurrence A of $MedicalVisit > 184 day(s) starts before start of "Measurement Period"
First: $MedicalVisit < 12 month(s) starts before start of "Measurement Period"
Most Recent: $MedicalVisit <= 184 day(s) starts before start of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of Occurrence A of $MedicalVisit
Most Recent: $MedicalVisit < 6 month(s) starts after start of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of First: $MedicalVisit <= 184 day(s) starts before start of "Measurement Period"
Most Recent: $MedicalVisit < 184 day(s) starts before end of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of First: $MedicalVisit < 6 month(s) starts after start of "Measurement Period"
Looking use code to add a column to this table ("Meets Numerator Criteria") to indicate (utilizing 0 or 1) if the unique patient number satisfies the numerator medical visit criteria outlined below (utilizing a measurement period of 1/1/2020-12/31/2021). If all MedicalVisits per unique patient satisfy numerator criteria, all rows per unique patient number show as 1 in column "Meets Numerator Criteria", ELSE 0 :
| Patient Number | MedicalVisit | Meets Numerator Criteria |
|---|---|---|
| 12345 | 2/2/2019 | |
| 12345 | 8/8/2019 | |
| 12345 | 2/2/2020 | |
| 12345 | 10/10/2020 | |
| 12345 | 4/15/2021 | |
| 12345 | 11/23/2021 | |
| 18345 | 2/2/2018 | |
| 18345 | 8/8/2018 | |
| 18345 | 3/2/2019 | |
| 18345 | 9/8/2019 | |
| 18345 | 3/2/2020 | |
| 18345 | 11/10/2020 | |
| 18345 | 5/15/2021 | |
| 18345 | 12/23/2021 | |
| 15345 | 2/10/2018 | |
| 15345 | 8/10/2018 | |
| 15345 | 3/10/2019 | |
| 15345 | 9/10/2019 | |
| 15345 | 3/10/2020 | |
| 15345 | 11/10/2020 | |
| 15345 | 5/10/2021 | |
| 15345 | 12/10/2021 | |
| 11341 | 2/17/2018 | |
| 11341 | 8/17/2018 | |
| 11341 | 3/17/2019 | |
| 11341 | 9/17/2019 | |
| 11341 | 3/17/2020 | |
| 11341 | 11/17/2020 | |
| 11341 | 5/17/2021 | |
| 11341 | 12/17/2021 |
Numerator Criteria (All bold statements must be true to satisfy numerator = 1):
At least one visit occurs in the first semester of year prior to Measurement Period
AND: Intersection of:Occurrence A of $MedicalVisit > 184 day(s) starts before start of "Measurement Period"
First: $MedicalVisit < 12 month(s) starts before start of "Measurement Period"
Last visit in the second semester of year prior to Measurement Period >= 60 days from first visit in prior semester
AND: Intersection of:Most Recent: $MedicalVisit <= 184 day(s) starts before start of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of Occurrence A of $MedicalVisit
Last visit in the first semester of Measurement Period >= 60 days from first visit in prior semester
AND: Intersection of:Most Recent: $MedicalVisit < 6 month(s) starts after start of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of First: $MedicalVisit <= 184 day(s) starts before start of "Measurement Period"
Last visit in the second semester of Measurement Period >= 60 days from first visit in prior semester
AND: Intersection of:Most Recent: $MedicalVisit < 184 day(s) starts before end of "Measurement Period"
$MedicalVisit >= 60 day(s) starts after start of First: $MedicalVisit < 6 month(s) starts after start of "Measurement Period"
