Forum Discussion
Solved
Finding 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 constant for each Customer ID.
I wish to add a calculated column which will return the customer status of each customer while his first purchase.
in the example above, customer 123 first purchase date is 15/01/2022. therefore, according to his status changes dates his status while first purchase was "b" (15/1/2022 is between 01/01/2022 and 01/02/2022)
thanks for helping out.
Amit
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 )
6 Replies
hi akfir
try to add a column like:
StatusWhileFirstPurhcase2 = VAR _customer = [CustomerID] VAR _firstdate = [FirstPurchaseDate] RETURN MAXX( FILTER( TableName, TableName[CustomerID]=_customer &&TableName[StatusChangeDate]>=_firstdate &&TableName[PreviousChangeDate]<=_firstdate ), TableName[OldStatus] )i worked like:
