Please help a newbie powerpivot user who works with healthcare claims data.
I pulled 27 months of claims data and need a DAX formula to define each record PERIOD as CURRENT or PRIOR. Two date fields exist "Service Date" is the date medical service performed, and "Paid Date" is the date the claim was paid.
CURRENT PERIOD is defined as:
SERVICE DATE: 1/1/2019 to 12/31/2019
and PAID DATE: 1/1/2019 to 03/31/2020
PRIOR PERIOD is defined as:
SERVICE DATE: 1/1/2018 to 12/31/2018
and PAID DATE: 1/1/2018 to 03/31/2019
NOTE: all records are to be excluded that don't fit into the above conditions. For example service date 12/1/2018 and paid date of 5/1/2019. Each period is defined as 12 months of services that pay within 15 months of paid claims.
I pulled 27 months of claims data and need a DAX formula to define each record PERIOD as CURRENT or PRIOR. Two date fields exist "Service Date" is the date medical service performed, and "Paid Date" is the date the claim was paid.
CURRENT PERIOD is defined as:
SERVICE DATE: 1/1/2019 to 12/31/2019
and PAID DATE: 1/1/2019 to 03/31/2020
PRIOR PERIOD is defined as:
SERVICE DATE: 1/1/2018 to 12/31/2018
and PAID DATE: 1/1/2018 to 03/31/2019
NOTE: all records are to be excluded that don't fit into the above conditions. For example service date 12/1/2018 and paid date of 5/1/2019. Each period is defined as 12 months of services that pay within 15 months of paid claims.
