Monday, March 18, 2013 6:03 PM
I found some issues when I need to count New Customer and Old Customer.
This is the data sample about the task I'm working on :
The expected Result may like this :
Time Period --> 4/6/2012 - 4/8/2012
* New Customer of Robert = 1 (the value must be A3, because A3 has visited the store before the time period)
* Old Customer of Robert = 1 (the value must be A5, because this is first purchase of A5 during the time period)
Can someone help me to solve this issue using Measure or another approach ?
Thank You in advance.
Tuesday, March 19, 2013 7:53 AM
Riska, I took your table and with a separate dates table created a 2 minute test model here: Example Model
I basically created 3 measures.
The first simply counts the unique customers in a given context.
The second which is where the work is done is to filter that count of customers for basically two criteria, the date being before the time period stipulated and the customer being active in the time period stipulated. Although the formula looks a little tasty it isn't actually that complex and basically draws on Rob Collie's 'Greatest Formula in the World' technique along with an additional VALUES() of the customer column that basically presents the customers in the current context (i.e. the date range stipulated) to filter the fact table for just those customers.
The third comes up with new customers as the balancing figure between the first two.
[Old Customers]=CALCULATE( [theCount], VALUES(factData[Customer]), ALL(dimDate), FILTER(ALL(dimDate),dimDate[Date]<MIN(factData[Date])) )
[New Customers]=[theCount]-[Old Customers]
This assumes your main table is called factData and the additional calendar table is called dimDate.
I wasn't 100% sure initially that this is what you were trying to do, let me know if you have any difficulties.
- Edited by Jacob Barnett Tuesday, March 19, 2013 7:54 AM Formatting
Tuesday, March 19, 2013 9:58 AM
Thank for your respond on my question.
Just now I discussed about the requirement to count New Customer and Old Customer. Client changed their mind about how to define Old and New Customer. I have 2 tables about Transaction and Customer.
TRANSACTION Employee Amount TransDate Customer CustomerName 1 100 1/1/2013 A01 John 1 50 1/3/2013 A02 Robert 2 10 1/6/2013 A03 Jasper 3 20 2/6/2013 A04 Apache 4 30 2/7/2013 A05 Ronald 2 25 2/8/2013 A06 Richard 3 30 2/9/2013 A07 Anna 4 12 2/15/2013 A01 John CUSTOMER Customer Name JoinDate A01 John 12/1/2012 A02 Robert 12/3/2012 A03 Jasper 12/5/2012 A04 Apache 12/7/2012 A05 Ronald 12/30/2012 A06 Richard 2/1/2013 A07 Anna 2/10/2013
The expected result may like this :
- Customer will set/define about time period to display the result. In this case, I would like to set time period in February 2013.
Old Customer will be 3 (John, Apache, Ronald) --> means, Customer Count who joined / registered in database below Time Period (February 2013).
New Customer will be 2 (Richard, Anna) --> means, Customer count who join / register within Time Period (February 2013). Richard and Anna registered in database (JoinDate) in February 2013.
Note : I will use distinct. because when the same Customer visit the Store during the time period more than 1 time, I only count as 1 customer.
I used slicer as Time Period Filter (I took the Slicer Data from Transaction Table).
Riska Bagus S
Tuesday, March 19, 2013 11:20 AM
OK. I've uploaded another quick demo here: New Demo
The difference in this version is in the [Old Customer] which now looks like this:
=CALCULATE( [theCount], FILTER(dimCustomer, dimCustomer[JoinDate]<min(dimDate[Date])) )
Basically the customer table is related to the to the fact table but NOT the date table.
This means that I can select the date slicer which only filters my fact table but I can then filter my customer table to just dates less than the lowest date selected in the slicer which in this case is months but could be days/weeks/years also despite the fact that they are unrelated. Because of the relationship between the customer and the fact table, when I apply this filter to the customer table that in turn filters the fact table to just customer with a start date prior to the period selected.
Hope this makes sense.
- Marked As Answer by Riska Bagus Wednesday, March 20, 2013 2:18 AM
Wednesday, March 20, 2013 2:18 AM
I forgot to attach Calendar table as Slicer table. But you did it.
Thank you very much. It works like a charm. I have tried in my PowerPivot, and I got the solution from you.