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

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

Helper III

6 years ago

v-gizhi-msft can we add New Customers also with the existing formula?
New Customer means, Customers, having the transaction only in that particular date period (Slicer Min and MAX date ).

Thank&Regards
Mani deep.

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

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

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

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

Forum Discussion

customer behaviour

Related content

Matrix behaviour multiple parameters

Strange CLOSINGBALANCE behaviour

REMOVEFILTERS() Unexpected behaviour

Inconsistent cross-highlighting behaviour.

Power BI Clustered Column Chart Legend Behaviour