Forum Discussion
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...
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
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.
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???