how calculate reactivated customers

[Silver75]

Hi

I would like to calculate a  measure to obtain reactivated customer in Facts Table.

Reactivated Customers = last bought from last data >365 days

variable in facts table

Id customer

Id product

data

revenues

thank you for the support

[v-chuncz-msft]

@Silver75,

Here is one way for your reference.

Reactivated Customers =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Facts,
            Facts[Id customer],
            "last date", MAX ( Facts[date] ),
            "count", DISTINCTCOUNT ( Facts[date] )
        ),
        [count] > 1
            && COUNTROWS (
                FILTER (
                    Facts,
                    Facts[Id customer] = EARLIER ( Facts[Id customer] )
                        && Facts[date] < [last date]
                        && Facts[date]
                        > [last date] - 365
                )
            )
                = 0
    )
)
    + 0

[Silver75]

thank you for you solution but zero is the result per Product Id.

could you suggest to me how can i resolve it?

many thanks

[tsapat]

I would try to go at it step by step, using "intermediate" measures in the Customers table, that would just tell us if the customer had sales in each of the periods that we are comparing (I assume that the Sales measures for each period have been already calculated so that you can use them):

Has CY Sales = IF([Sales Current Period] > 0;1;0)

Has PY Sales = IF([Sales Previous Period] > 0;1;0)

Then, just create another measure to answer our question, like the following:

Reactivated = CALCULATE(DISTINCTCOUNT(Customers[Customer Code]);FILTER(Customers;[Has CY Sales] = 1); FILTER(Customers;[Has Prev Sales] = 1))

It is a bit more work than a single DAX formula, but I think it is easier to understand and possibly reused.