Hello Emre,

The calculated field you provide itself is OK, but it depends on how you use it on your view.

For example,

in this view, SUM(Calculatoin1) summerizes the CAC of the customers who have made order in each year.

So could you provide a sample workbook for further advise?

Regards.

Hi Emre,

Can you elaborate your business question? I will also suggest you to provide the sample data (Dummy preferable) in order to visualize in the best possible way. Would you like to see the CAC at the year level or at the Customer level? I would also suggest you to provide the sample calculation for the one of the cases in excel which will provide better clarity and we can validate the output at our end.

If you calculate the CAC in above way you will get the same customer for the all the year along with the avg CAC. what I think is that Customers are acquired in any one of the business year and later they are retained. I think you should be only worried about the new customer for each and every year and not the all customers since you have acquired few customers for the year.

I hope this helps in someway. Let us know clear business logic. Whatever mentioned above is just my view point and I am no expert.

Thanks and Regards,

Ashish Chaudhari

Hi Emre!

Probably you need to join at Customer ID=Customer ID  and Join Date=Order Date.

If it does not give you what you are looking for then please share the required output value. It would be great help

Regards,

Sireesha.

Hello Emre,

Please have a try as follows,

CAC2:

IIF(YEAR([Join Date])=YEAR([Order Date]),[CAC],0)

Marketing Total Cost2:

{INCLUDE [Customer ID] :MAX([CAC2])}

The difference of two calculations,

Finally, the result is,

Regards