Replicate result on Matrix to Chart

[GonzaloB]

Hi guys,

I'm trying to build al CLV model and for that I have a calendar table which relates to customer acquisition cost on one side (monthly), customer table which in turn relates to sales tables (fact table)

Now I've managed to do quite a few things but my manager wants me to replicate this chart and I could really use some help.

So far I've managed to show something on a matrix using calculates, but this is far from ideal. 

The measures involved are:

Cohort year (year of customer creation date, I'm using a filter from rolling calendar)

LTV (sum of revenue from sales)

Acquisition cost (monthly)

I managed to create a calculated column inside the fact table (I know this is also far from ideal) and thought that that might save me, but alas it didn't. This calculated columns calcualts the days between when the order was placed and the customer was created, giving me 'maturity days'

I was hoping to drop maturity days as axis, calendar.year as legend and (sum of revenue / cost of acquisition) as values. but it doesn't seem right. I think I'm dividing every sales revenue by the monthly customer acquisition cost, but I don't know how to fix this; it works on a table it does not work on the chart.

I hope this made sense, please let me know if you need more information and thank you for any assistance you can provide.

I'm afraid I can't understand M and consider myself beginner/intermediate when using DAX.

The fact is that on the chart I should be seeing the cumulative sales revenue divided by the initial cohort acquisition cost, hence the ltv/cac should increase as you move further on the axis 

Thanks

[v-stephen-msft]

Hi @GonzaloB ,

May I ask what is wrong? Are you saying that the data is wrong, or the style of the chart is wrong?

Please provide me with more detailed information about your tables, it would be great if you could share a virtual sample data.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GonzaloB]

Hi,

Apologies for the confusion allow me to try and clarify.

Rolling Calendar: simple has very date and I connect through this

Acquisition Cost: monthly spent for capturing new customers

Customer table: Has customer creation date as well as details 

Sales table: every sale the company experienced as well as the Customer who made the purchase

I need a chart that shows the accumulated revenue per customer cohort divided by the cohort acquisition cost.

Basically, its Output 1 Divided by Output 2

[GonzaloB]

I really wished the reply window was bigger, and for some reason I cannot paste formated tables or add the attached excel file... something along the lines of HTML...

Please excuse me for posting like this:

Output 1 - Acquisition Cost per Customer cluster
Customer Cohort Acquisition cost <30d Acquisition cost <90d Acquisition cost <1000d
1/06/2021 171,667 171,667 171,667
1/10/2020 156,667 156,667 156,667
1/11/2020 171,667 171,667 171,667

Output 2
Revenue per Customer Cluster Revenue cost <30d Revenue cost <90d Revenue cost <1000d
1/06/2021 168 168 633
1/10/2020 975 975 1360
1/11/2020 115 206 206

Expected Output
Cust Cohort Payoff < 12months 12<Payoff < 24months 24<Payoff < 36months
Jun-20 0.00098 0.00098 0.00369
Sep-20 0.00622 0.00622 0.00868
Nov-20 0.00067 0.00120 0.00120




Sample tables
Acquisition cost
Date Acquisition Cost New Customers CAC
1/06/2021 $ 171,667 415 $ 414
1/07/2020 $ 120,000 376 $ 319
1/08/2020 $ 110,000 341 $ 323
1/09/2020 $ 150,000 446 $ 336
1/10/2020 $ 156,667 364 $ 430
1/11/2020 $ 171,667 415 $ 414

Customer
Creation Date Customer ID Customer details
3/06/2020 1 v
8/06/2020 2 x
2/10/2020 3 y
5/11/2020 4 z

Rolling Calendar
Date Day Month Year
1/07/2020 1 7 2020
2/07/2020 2 7 2020
2/07/2020 2 7 2020
3/07/2020 3 7 2020
3/07/2020 3 7 2020
4/07/2020 4 7 2020

Sales
Date Order ID Customer ID SKU Qty Price Revenue Customer creation Order Maturity (days)
2/07/2020 1114522 1 A 5 5 25 3/06/2020 29
2/07/2020 1114522 1 B 4 13 52 3/06/2020 29
2/07/2020 1114522 1 C 3 22 66 3/06/2020 29
2/07/2020 1114523 2 A 5 5 25 8/06/2020 24
30/10/2020 1114524 3 B 75 13 975 2/10/2020 28
1/12/2020 1114525 4 C 4 22 88 5/11/2020 26
1/12/2020 1114525 4 A 1 5 5 5/11/2020 26
1/12/2020 1114525 4 C 1 22 22 5/11/2020 26
1/12/2020 1114526 1 A 1 5 5 3/06/2020 181
1/12/2020 1114526 1 B 2 13 26 3/06/2020 181
2/02/2021 1114527 2 A 4 5 20 8/06/2020 239
2/02/2021 1114528 1 A 5 5 25 3/06/2020 244
2/02/2021 1114528 1 B 2 13 26 3/06/2020 244
2/02/2021 1114528 1 C 4 22 88 3/06/2020 244
2/02/2021 1114528 1 D 5 55 275 3/06/2020 244
2/02/2021 1114529 3 D 7 55 385 2/10/2020 123
2/02/2021 1114530 4 B 7 13 91 5/11/2020 89

[GonzaloB]

I attached the pictures for clarity.