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

 

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

6 Replies

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

    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's avatar
      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'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