Ok to fix this I am getting rid of all the information that isn’t relevant.
I think the hardest thing with this in terms of getting my mind around it is there’s just so much in the model and it can get confusing. I really like to drill into the very specific things we are looking at and avoid any distraction.
These calcs are already quite advanced so the easier I can make it the better.
First I deleted every page, then cleaned up the model a little.
Now I’m only going to look at the singular DAX measure here.
And then I’m going to start breaking it down.
See below.
Is this right? Both the first and last date are feeding into the won clients measure
So we are calculating new client every week
Won Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
DATESBETWEEN ('Calendar'[FullDate],
[First Date Current Period],[Last Date Current Period]) )
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
DATESBETWEEN('Calendar'[FullDate],
[First Date Prior Period], [Last Date Prior Period] ) )
RETURN
COUNTROWS(
EXCEPT(CustomerLTM,PriorCustomers))
Now when I add lost client to the mix
First I would go through and double check all these all calculating correctly here. Maybe you have I’m not sure.
Then I set this up to check around who are won/lost clients.
It is producing a result so might be correct not sure.
Now going into what products
I tried to replicate the scenario
Seems to be showing similar answers which is good.
Expanded on this one.
Ok then I finally have replicated this also
Yes and can see the difference between product 35 and 59.
Ok so now need to look back into everything leading up to this number.
Will focus on this one result.
If I look on the face of it interesting to note here the product 59 appears but the total is still 4
So why is that.
Let’s look at the calculation in that context
Lost Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
DATESBETWEEN ('Calendar'[FullDate],
[First Date Current Period],[Last Date Current Period]) )
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
DATESBETWEEN('Calendar'[FullDate],
[First Date Prior Period], [Last Date Prior Period] ) )
RETURN
COUNTROWS(
EXCEPT(PriorCustomers,CustomerLTM))*-1
At each row we are taking a table of customers and then comparing them to a table of customers from another period.
But we have to remember here the context of this calculation is not only by the date but also by product now as well. So the product index is filtering this calculation also.
I wanted to see this person actual transactions
Seems this is what is getting picked up.
So why is this?
I’m looking at the date here 21/6/2015 - it was in the PriorCustomers table, but not in the CustomerLTM table,
So in my estimation it is calculating as it should
So then why isn’t it calculating here?
My guess is that the customer did actually purchase something else though, which is seems they have
So what you’re not taking into account here is the additional filter of the product in my view.
You have to remember that you’re now saying when in the product context has a group of customer bought this particular product again over the time horizon, NOT just anything which is the case when looking at the results against just the customer context.
Ok this is enough for now, I’ve spent a lot of time reviewing this and writing out this answer.
Attached.
181029 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2 (2).pbix (251.2 KB)
