Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago
Solved

customer behaviour

Below is my data ID DATE (DD/MM/YYYY) 4 01/04/2017 2 01/01/2017 1 01/09/2017 1 01/08/2017 1 1/1/2018 3 31/01/2019 4 01/08/2018 ACTIVE CUSTOMER: If the customer having the transaction in bet...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    I add some new data to the original test table:

    I define this new customer status as 'New'.

    Please try this measure:

    Status =
VAR MinDate =
    MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
    MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
    IF (
        MONTH ( MinDate ) <= 6,
        DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
        DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                'Table'[TransactionDate] <= MaxDate
                    && 'Table'[TransactionDate] >= MinDate
            )
        ) <> 0,
        SWITCH (
            TRUE,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    'Table',
                    'Table'[TransactionDate] > InPast6MonthsDate
                        && 'Table'[TransactionDate] < MinDate
                )
            ) <> 0, "Active",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0, "Reactive",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < MinDate )
            ) = 0, "New"
        ),
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0,
            "Inactive"
        )
    )

    And change the original status table to this:

    The result shows:

    Here is my changed pbix file:

    pbix 

     

    Best Regards,

    Giotto Zhi

manideep547's avatar
manideep547
Helper III
6 years ago

v-gizhi-msft  Your Measure is not working, Because I have 4 Trancations tables those tables have relationships with Date slicer , below is my relationship 

 Slicer Table[Date], Table A[Date] Active Relationship many to one
 Slicer Table[Date], Table B[Date] Active Relationship many to one
 Slicer Table[Date], Table C[Date] Active Relationship many to one
 Slicer Table[Date], Table D[Date] Active Relationship many to one

 

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try to combine these four tables by UNION and SELECTCOLUMNS:

Union Table =
UNION (
    SELECTCOLUMNS (
        TableA,
        "CustomerID", TableA[CustomerID],
        "TranscationID", TableA[TranscationID]
    ),
    SELECTCOLUMNS (
        TableB,
        "CustomerID", TableB[CustomerID],
        "TranscationID", TableB[TranscationID]
    ),
    SELECTCOLUMNS (
        TableC,
        "CustomerID", TableC[CustomerID],
        "TranscationID", TableC[TranscationID]
    ),
    SELECTCOLUMNS (
        TableD,
        "CustomerID", TableD[CustomerID],
        "TranscationID", TableD[TranscationID]
    )
)

 

Best Regards,

Giotto Zhi

  • manideep547's avatar
    manideep547
    Helper III
    6 years ago

    v-gizhi-msft 

    Measure = 
VAR MinDate =
    MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
    MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
    IF (
        MONTH ( MinDate ) <= 6,
        DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
        DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                'Table'[TransactionDate] <= MaxDate
                    && 'Table'[TransactionDate] >= MinDate
            )
        ) <> 0,
        SWITCH (
            TRUE,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    'Table',
                    'Table'[TransactionDate] > InPast6MonthsDate
                        && 'Table'[TransactionDate] < MinDate
                )
            ) <> 0, "Active",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0, "Reactive"
        ),
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0,
            "Inactive"
        )
    )
    For the above formula can we add the new customers also 
    new customers mean the customers who have the transactions  only in that particular period of time (Slicer date)
    • v-gizhi-msft's avatar
      v-gizhi-msft
      Community Support
      6 years ago

      Hi,

       

      I add some new data to the original test table:

      I define this new customer status as 'New'.

      Please try this measure:

      Status =
VAR MinDate =
    MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
    MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
    IF (
        MONTH ( MinDate ) <= 6,
        DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
        DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                'Table'[TransactionDate] <= MaxDate
                    && 'Table'[TransactionDate] >= MinDate
            )
        ) <> 0,
        SWITCH (
            TRUE,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    'Table',
                    'Table'[TransactionDate] > InPast6MonthsDate
                        && 'Table'[TransactionDate] < MinDate
                )
            ) <> 0, "Active",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0, "Reactive",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < MinDate )
            ) = 0, "New"
        ),
        IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
            ) <> 0,
            "Inactive"
        )
    )

      And change the original status table to this:

      The result shows:

      Here is my changed pbix file:

      pbix 

       

      Best Regards,

      Giotto Zhi