Define Period based on 2 Dates

Anything related to PowerPivot and DAX Formuale
cmacvp16
Posts: 1
Joined: Thu May 14, 2020 2:10 am

Define Period based on 2 Dates

Postby cmacvp16 » Thu May 14, 2020 2:26 am

Please help a newbie 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.

MattAllington
Posts: 1091
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Define Period based on 2 Dates

Postby MattAllington » Fri May 15, 2020 7:43 am

You can simply do this in a calculated column. It is pretty easy using and IF statement. The syntax is almost the same as Excel.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests