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")
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
Anonymousdid you try to tweak context as follow?
Start Date = CALCULATE(MIN(Sheet1[Start]),ALLEXCEPT(Sheet1,Sheet1[Patient ID],Sheet1[Part Number]))
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?