cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
surfingjoe Regular Visitor
Regular Visitor

If based on first date occurrence

Working with Pharmaceutical data.  Trying to determine the first occurrence of a prescription as "New" prescription and all successive orders of that prescription as "refills"

 

Each patient will have a "service start date" and a "service stop date".   Each service range can have multiple fills.

 

A doctor can renew a prescription, we would then assign a new "service start date"

 

I'd like to be able to use an if statement stating that the first start date is a "New" service start and all successive start dates as refills.

 

If this was the AdventureWorks data, then it can be described as wanting to flag the very first customer order for a product as "new order" for that product and if the customer oders more of the same product, then flag it as a "repeat" order for that same product.

 

Bottom line I'd like to create a slicer for only new prescriptions.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: If based on first date occurrence

@surfingjoe

 

Try this for the new column:

 

NewColumn=
VAR _StartDate = CALCULATE ( MIN ( Query1[Start date of service] ), ALLEXCEPT ( Query1, Query1[Product], Query1[Customer] ) )
RETURN
IF( Query1[Start date of service] = _StartDate, "New", "Refill")

 

9 REPLIES 9
Super User
Super User

Re: If based on first date occurrence

Hi @surfingjoe

 

I guess each  prescription has a unique identifier? Why don't you show a sample of your data model? It would make things much easier for people trying to help. 

You could use an ascending RANKX( ) based on date within each prescription  and assign a "Yes" to the row ranked number  1. Or you can also check whether the date for the current row has the MIN(date) for that prescription.

itsmebvamsi Regular Visitor
Regular Visitor

Re: If based on first date occurrence

@surfingjoe 

 

Create a Calculated column with following code

 

 

Start Date = CALCULATE(MIN(Sheet1[Start]),ALLEXCEPT(Sheet1,Sheet1[Patient ID]))

Create slicer column with following code 

 

 

 

Flag = IF(Sheet1[Start]=Sheet1[Start Date],"Start","Refill")

play around with the attached PBIX.

surfingjoe Regular Visitor
Regular Visitor

Re: If based on first date occurrence

Not sure if I was clear.  Need first date for each oder per product per customer

So the new column should be perhaps

Start Date = CALCULATE(MIN(Query1[Start date of service]),ALLEXCEPT(Query1,Query1[Part Number]))
 
 
BUT:
However, the above calculated column returns the same start date for all part numbers which is the first start date for all customers and/or products
 
therefore it is not returning a unique start date for the first time a product is ordered for each customer or in my case the first prescription for a specific drug for that customer
Super User
Super User

Re: If based on first date occurrence

@surfingjoe

 

Then

Start Date =
CALCULATE (
    MIN ( Query1[Start date of service] ),
    ALLEXCEPT ( Query1, Query1[Product], Query1[Customer] )
)

should suffice for the start date. You can, within the code for the column, check whether the current row date equals the start date.

Highlighted
itsmebvamsi Regular Visitor
Regular Visitor

Re: If based on first date occurrence

@surfingjoedid you try to tweak context as follow? 

 

Start Date = CALCULATE(MIN(Sheet1[Start]),ALLEXCEPT(Sheet1,Sheet1[Patient ID],Sheet1[Part Number]))
surfingjoe Regular Visitor
Regular Visitor

Re: If based on first date occurrence

Thank you, that definitely gives me the first "start date" for each product for each patient.

 

However now, I'm really going to show my newbieness.  the if statement shown above seems like it should work comparing the row's start date with the new columns start date, however, when I try to write out the if statement, it is only allowing me to type in measures and not actual column names to compare.  

 

What am I doing wrong, just need to create an if statement that compares start date to the new column???

surfingjoe Regular Visitor
Regular Visitor

Re: If based on first date occurrence

Thank You that works.

 

however I've an issue with the if statement.  PowerBI is only allowing me to type in measures to do the logical test.  not columns, am I doing something wrong?

Super User
Super User

Re: If based on first date occurrence

@surfingjoe

 

Try this for the new column:

 

NewColumn=
VAR _StartDate = CALCULATE ( MIN ( Query1[Start date of service] ), ALLEXCEPT ( Query1, Query1[Product], Query1[Customer] ) )
RETURN
IF( Query1[Start date of service] = _StartDate, "New", "Refill")

 

surfingjoe Regular Visitor
Regular Visitor

Re: If based on first date occurrence

That is exactly what I was trying to acheive.  Thank You

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 53 members 1,018 guests
Please welcome our newest community members: