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

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity 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
Icon for Community Champion rankCommunity 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