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 )

     

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

yes please.

have a look in customer 456. First Purchase Date is later than latest Status change date. therefore value should be "d"

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
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 )

 

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

    Perfect thanks!