Forum Discussion

akfir's avatar
akfir
Icon for Helper V rankHelper V
3 years ago
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 c...
Between dates
calculated column
status
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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 )

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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:

 

akfir's avatar
akfir
Icon for Helper V rankHelper V
3 years ago

thanks for your quick reply!
it seems good but fail to show values in cases where FIRST PURCHASE DATE is larger than any status change date and then it returns BLANK instead of i guess NEW Status