Customers Eangagement Within A Period Of Marketing Campaign With M2M Relationships

Dear Experts

I have a table called campaign with the following columns:
CNO : Campaign No
Start Date
End Date
Campaign Cost

This Table have 1:m with Table customers to indicate which customers belonging to a campaign
as shown in the pciture:

I need to calculate how many customers engage in each campaign and how much they spent
and if the Total spent by the customer withing this period covered the expences of the campaign
or not.
The challange that I have is I’m not able to active the relationship between Date and
and Campaign table so what sort of changes that I need to overcome this challange
either by changing the relationship of the structure or do DAX script to do this.

Note : Already I did two measures [Total Sales] and [Total Customers] To calculate the sales and No of customers, The resources attached with this email.

Customer Data 1.3_RFM Trail.pbix (1.5 MB) Learning Summit Nov18 - Data Model.xlsx (3.9 MB)

You can merge campaign with customers table.

Customer Data 1.3_RFM Trail.pbix (1.2 MB)

1 Like

Hi @AntrikshSharma

I think you Misunderstand the Table name, I need to the calculation for Table (Campaign) and you did it for another table (Scinario Analysis) not the one that I mentioned, In addition the two tables (Campaign and Customers) already have 1:m model same as you did but sorry with different Table, so still the challange remains not solved.

@AntrikshSharma Additionally I didn’t found the table(Campaign) in the Pbix file did you remove it?

Campaign is merged with customers, I didn’t remove it, it is in Power query just no loaded into the model.

Ok, Thank you I understand , Kindly how can I do the calculation I mentioned it on the question?

@AntrikshSharma can you tell me how we do the calculations?

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!