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 h...
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")

     

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.

  • AlB's avatar
    AlB
    Community Champion
    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")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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???