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

     

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.

      • 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")

         

    • itsmebvk's avatar
      itsmebvk
      Continued Contributor
      7 years ago

      Anonymousdid you try to tweak context as follow? 

       

      Start Date = CALCULATE(MIN(Sheet1[Start]),ALLEXCEPT(Sheet1,Sheet1[Patient ID],Sheet1[Part Number]))
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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?