Forum Discussion
akfir
Helper V
Helper VFinding customer status for a given date
Hi Masters, Given is a table of status changes of customers (Old Status, New Status, Change Date, Previous Change Date) as described: Also given is the column "First Purchase Date" which is c...
hi akfir
then try like:
StatusWhileFirstPurhcase2 = VAR _customer = [CustomerID] VAR _firstdate = [FirstPurchaseDate] VAR _value1 = MAXX( FILTER( TableName, TableName[CustomerID]=_customer &&TableName[StatusChangeDate]>=_firstdate &&TableName[PreviousChangeDate]<=_firstdate ), TableName[OldStatus] ) VAR _value2 = MAXX( TOPN( 1, FILTER( TableName, TableName[CustomerID]=_customer ), TableName[StatusChangeDate] ), TableName[NewStatus] ) RETURN IF( _value1<>BLANK(), _value1, _value2 )
akfirHelper V
Helper Vyes please.
have a look in customer 456. First Purchase Date is later than latest Status change date. therefore value should be "d"
FreemanZSuper User
hi akfir
then try like:
StatusWhileFirstPurhcase2 = VAR _customer = [CustomerID] VAR _firstdate = [FirstPurchaseDate] VAR _value1 = MAXX( FILTER( TableName, TableName[CustomerID]=_customer &&TableName[StatusChangeDate]>=_firstdate &&TableName[PreviousChangeDate]<=_firstdate ), TableName[OldStatus] ) VAR _value2 = MAXX( TOPN( 1, FILTER( TableName, TableName[CustomerID]=_customer ), TableName[StatusChangeDate] ), TableName[NewStatus] ) RETURN IF( _value1<>BLANK(), _value1, _value2 )- akfir
Perfect thanks!