Can you give me some direction on how to approach this situation?
I’ve created a simple dashboard showing the results of a new product (CD) that we have just started offering. It shows how many, the $ amount, locations sold, etc (basic, easy stuff). I now would like to break those totals down into new customers vs. existing customers (note: I did watch your recent video). The challenge I’m having is with how we define a new customer.
A new cust. is someone who opens a savings (SV) product (everyone must have it) and within the ‘new’ date range (i.e. latest month). This is also fairly easy to do until I try to incorporate it with the ‘CD’ product being promoted.
For example, the ‘New’ designation would need to be someone with an SV & CD in the most recent month. Because my model is at the transaction level, no one row is going to have a SV & CD so everything I’ve tried so far is giving me ‘Blank’.
I’ve tried a calculated column with the New and Existing labels but that is also not working the way that I’ve tried it.
As I see it, I somehow need to get this into a customer view. I do have an Account Number field that would be the same for every account owned by the customer (CD, SV, and other). I can’t figure out how to make use of it in this situation.
Any thoughts? Can you point me in the right direction?
Really need to see some images and formulas. Finding it hard to imagine everything.
What you will likely want to do is create a virtual table of you customers and evaluate in the current context if they have both the SV and CD. If yes retain them in the table. The finish you would likely complete a COUNTROWS.
This is quite similar to the recent tutorial, just with slight differences to the virtual tables within the EXCEPT function.
Bonus CD Total Accounts = CALCULATE(count(SAVINGS[PARENTACCOUNT]),
SAVINGS[TYPE]=107, // a CD
SAVINGS[CLOSEDATE]=0)
Bonus CD Curr Balance = CALCULATE(SUM(SAVINGS[BALANCE]),
SAVINGS[TYPE]=107,
SAVINGS[CLOSEDATE]=0)
Below is my attempt at the virtual new customer table as described in your video. This level of DAX is way above my skill so there is no doubt it is wrong.
New Customer Count =
VAR
Customers = VALUES(SAVINGS[PARENTACCOUNT])
Return
CALCULATE(COUNTROWS(Customers),
SAVINGS[TYPE]=107, // this is a CD
SAVINGS[TYPE]=0, // This is the SV
SAVINGS[DEP OPENDATE]>=DATE(2018,3,1))
//both the SV & CD must have been opened since Apr 1st (actually Apr 1-May 30 might be safer)
I would then do this same measure (above) to calculate new customers new CD balances.
I’m already showing total CD accounts opened and CD balance acquired. My end goal is to show new customer CD count vs existing customer (and the same for balance) in a Pie (or other appropriate) chart.
If this is too vague for you to assist, I understand. I already have a better idea.
CALCULATETABLE( VALUES( CustomerIDs ), FILTER( … CD etc… )
Then you’ll want to compare both these table and see if customer exist in both.
You can do this with the EXCEPT function (like showcased in the workshop)
EXCEPT( SV Customers, CD Customers )
Then all you should need to do from there is wrap this inside a COUNTROWS function.
The end logic here is: How many customers (in the current context) open a SV product AND also open a CD product.
See how you go with this.
The logic and formulas I’ve worked through here are really similar to the ones from the workshop, with just a small adjustment based on your requirements
Thanks - Worked perfectly! Great to learn something new. There is probably an easier way to do it but these were my additions…
One last question, if you don’t mind. In the measures above, I included dates (representing CD promotion date). I didn’t include a date slicer in my report. If I had, the date slicer should work with these measures so I wouldn’t need to include dates in the measures. In that case, I might have to add “Filter” statements to my measures instead of using the embedded filters (in Calculate/Calculate Table) because those ignore page filters. Does that sound correct?
Thank you for your help on this! Wish I could show the final report.
Yes your right on the INTERSECT option there. I got that incorrect, because your looking for the customer in both. See yes INTERSECT is the right table function to use in this instance.
Yes you can also definitely use FILTER within you CALCULATETABLE variable to achieve what you want.
I would definitely recommend making this dynamic somehow with the date because your consumers will likely have no idea what’s hard coded in the formulas.
