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 Everyone,
I need help in determing the same old question but the data structure is not letting my head think straight - finding new customers.
I got two tables with folliwng fields
-Events
-Customers
-DateTable
I want to see the numbef of new customers who have attended the events say in last month or last year.
I have to identify the customers for any event from customer table and all details of events in events table
So the dates and events will be coming from events table and customers who attended those events from customer table-
In order to get new customers say in 2018, I would need to find all events for 2018 and then all customers who attended those events( from customer table) but then need to see who have not attended anything before 2018.
Any help would be greatly appriciated.
Thanks
emudria
Solved! Go to Solution.
Hi Emudria,
Please download the demo in the attachment. The [Measure] shows the details while the [Result] shows the expected result.
Result = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Customers[CustomerID] ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Best Regards,
Dale
It's my pleasure.
Since I don't have the real data, the order of the events could be complicated. Anyway, you can sort them by the orders you like then add an index to fix it. The logic is similar. Please refer to the formulas below and the snapshot.
Measure 2 = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) ) ) RETURN CALCULATE ( CONCATENATEX ( VALUES ( Customers[CustomerID] ), [CustomerID], "-" ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Result 2 = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Customers[CustomerID] ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Best Regards,
Dale
Hi Emudria,
Please download the demo in the attachment. The [Measure] shows the details while the [Result] shows the expected result.
Result = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -1 ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Customers[CustomerID] ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Best Regards,
Dale
@v-jiascu-msft OMG! have no words to thank you. It works exactly the way it was needed - beautiful logic applied.
Is it possible to modify this so that if I put a filter on an event, it gives me new memebers (count & ID ) for that event. Currently it works with date filter but you know on an event filter it will give all customers as new customers.
Thanks
It's my pleasure.
Since I don't have the real data, the order of the events could be complicated. Anyway, you can sort them by the orders you like then add an index to fix it. The logic is similar. Please refer to the formulas below and the snapshot.
Measure 2 = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) ) ) RETURN CALCULATE ( CONCATENATEX ( VALUES ( Customers[CustomerID] ), [CustomerID], "-" ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Result 2 = VAR beforeCurrentCustomers = CALCULATETABLE ( VALUES ( Customers[CustomerID] ), FILTER ( ALL ( Events ), Events[Index] < MIN ( Events[Index] ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Customers[CustomerID] ), FILTER ( 'Customers', NOT 'Customers'[CustomerID] IN beforeCurrentCustomers ) )
Best Regards,
Dale
Thank you Dale - Works like a charm - It is a happy Friday 🙂
My pleasure. There will be happy weekends too.
Best Regards,
Dale
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |