customer behaviour

[manideep547]

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 between min and max dates in the slicer and also having the
transaction in the past less than 6 months starting from the min date in the slicer.
Example :
If I selected 01/01/2018 to 01/01/2019 In that period the customer have the transaction and also have the transaction in between 01/07/2017 to 01/01/2018 then consider as the Active customer

Inactive CUSTOMER
If the customer not having the transaction in between min and max dates in the slicer and also having the transaction in past + 6 months starting from the min date in slicer then consider as Inactive customer
Example:
If I selected 01/01/2018 to 01/01/2019 In that period the customer have the transaction and also have the transactions not in between 01/07/2017 to 01/01/2018 then consider as the Inactive customer

Reactive CUSTOMER:

If the customer having the transaction in between min and max dates in the slicer and also having the transaction in past + 6 months starting from the min date in slicer then consider as Reactive customer
Example:
If I selected 01/01/2018 to 01/01/2019 In that period the customer has the transaction and also have the transactions not in between 01/07/2017 to 01/01/2018 then consider as the Reactive customer



OUTPUT (If i selected date from 1/1/2018 to 1/1/2019 in slicer )

count of Active customers : 1(ID ="1")
count of Inactive CUSTOMER : 1(ID="2")
count of Reactive CUSTOMER : 1(ID="4")


ID       DATE                    RELATION
1       01/01/2018           Active
2       01/01/2017           Inactive
4       01/08/2018           Reactive
Below is my Actual DATa

[v-gizhi-msft]

Hi,

Accotding to your description, i create a table to test:

Then create a date slicer table:

Date Slicer = CALENDAR(MIN('Table'[TransactionDate]),MAX('Table'[TransactionDate]))

Create a measure to show each customer's status:

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"
        )
    )

Then create a status table by 'Enter Data':

Create a measure to count rows:

Measure = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[CustomerID],"Status",[Status]),[Status] in FILTERS('Status'[Status])))

Choose the data from date slicer table as a slicer visual, when select the date duration, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[manideep547]

@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]

@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]

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]

@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 · Accepted solution]

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