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 

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