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

 

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