Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Anonymous

     

    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

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    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.

  • itsmebvk's avatar
    itsmebvk
    Continued Contributor
    7 years ago

    Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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
      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

         

        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.