Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |